Add formula syntax error

AdamC
AdamC Member
in Magic ETL

Hi all,

I am brand new and trying to swim in the deep end and don't have knowledge of SQL so struggling with how to write a case/formula for one of my ETL transformations.

I want to create a column with a constant based on values in a column, essentially:

If the value of column A equals ONEY or CMDU than 45, if not than 30

For some reason an error keeps coming back that the value ONEY is not found and that the syntax is wrong. So maybe I have an additional problem beyond the syntax?

Best Answer

  • RobSomers
    RobSomers Coach
    Answer ✓

    @AdamC Welcome to Domo and SQL! So your statement will look like this:

    case when `Column A` IN('ONEY','CMDU') then 45 else 30 end

    or

    case when `Column A` = 'ONEY' or `Column A` = 'CMDU' then 45 else 30 end

    Your CASE WHEN is basically your IF. I'm guessing one problem you were running into is that if the data in your column is a string (basically text, though that's not always true) then you need to put single quotes around the value.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • RobSomers
    RobSomers Coach
    Answer ✓

    @AdamC Welcome to Domo and SQL! So your statement will look like this:

    case when `Column A` IN('ONEY','CMDU') then 45 else 30 end

    or

    case when `Column A` = 'ONEY' or `Column A` = 'CMDU' then 45 else 30 end

    Your CASE WHEN is basically your IF. I'm guessing one problem you were running into is that if the data in your column is a string (basically text, though that's not always true) then you need to put single quotes around the value.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @AdamC Domo predominately uses MySQL syntax.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • AdamC
    AdamC Member

    Thanks Rob. That worked the first go round. I think I will play with a bit and try to add some other things just to get more familiar, but that really helped me over the hump.

    Jae - thanks for that. I didn't realize that about mySQL so if I do some learning I will head that direction.

    Thanks again!

Categories