If condition between two columns

DanieleSilva
DanieleSilva Member
in Magic ETL

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.

Best Answers

  • david_cunningham
    david_cunningham Coach
    edited May 2024 Answer ✓ 
    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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

Answers

  • david_cunningham
    david_cunningham Coach
    edited May 2024 Answer ✓ 
    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    DanieleSilva Member

    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
    david_cunningham Coach

    @DanieleSilva

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

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • DanieleSilva
    DanieleSilva Member

    Thanks David! It worked!

Categories