If condition between two columns

Options

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
    edited May 21 Answer ✓
    Options
    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 ✓
    Options

    @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
    edited May 21 Answer ✓
    Options
    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 ✓
    Options

    @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
    Options

    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
    Options

    @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
    Options

    Thanks David! It worked!