If condition between two columns

DanieleSilva

Hello, could you help me with a calculation to create a new column in my table: If the "Fixed Value" column has a value, add "Fixed Contract" but if the "Spot" column has a value, put "Spot Contracts" in the newly created column.

david_cunningham

case
  when Fixed Value is not null then "Fixed Contract"
  when Spot is not null then "Spot Contract"
  else "Undefined"
END

I don't have your actual data, so this is assuming that there is never a time when both "Fixed Value" and "Spot" are both populated, and that you don't have to check any other conditions. I threw in an ELSE to catch other cases.

MichelleH

@DanieleSilva You can use the following case statement either in Beast Mode or an ETL formula tile:

case 
  when `Fixed Value` is not null then 'Fixed Contract' 
  when `Spot` is not null then 'Spot'
end

david_cunningham

case
  when Fixed Value is not null then "Fixed Contract"
  when Spot is not null then "Spot Contract"
  else "Undefined"
END

I don't have your actual data, so this is assuming that there is never a time when both "Fixed Value" and "Spot" are both populated, and that you don't have to check any other conditions. I threw in an ELSE to catch other cases.

MichelleH

@DanieleSilva You can use the following case statement either in Beast Mode or an ETL formula tile:

case 
  when `Fixed Value` is not null then 'Fixed Contract' 
  when `Spot` is not null then 'Spot'
end

DanieleSilva

Thank you for sending this and sorry for the delay. This is my first time using DOMO, so I may be doing something wrong because I couldn't do it. I would like to know which ETL option I should use to create the "Fixed Contract" and "Spot Contract" columns. The columns I have as Fixed Value and Spot (BRL) are numeric.

david_cunningham

@DanieleSilva

You would use the Formula Tile in MagicETL and then use the code provided above. You can name this column whatever you want.

DanieleSilva

Thanks David! It worked!