Case Syntax

mhouk
mhouk Member
in Magic ETL

I am having difficulty understanding the case syntax while using magic ETL.

In the current Example, I have a list of properties and coded them to display a text column of the region for each specific property. Unfortunately, there is no address line to be able to parse and use to make the regions so I'm hard-coding the numbers.

Case when `lookup_code`= 1026 THEN "Charlotte"

ELSE WHEN `lookup_code`= 1024 THEN "Charlotte"

ELSE WHEN `lookup_code`=1020 or `lookup_code`= 1009 or `lookup_code`=1023 THEN "Raleigh/Durham"

  ELSE WHEN `lookup_code`=1019 or `lookup_code`= 1010 or `lookup_code`= 1016 or `lookup_code`=3001 THEN "Dallas"

ELSE WHEN `lookup_code`=1014 or `lookup_code`=1022 or `lookup_code`=1006 or `lookup_code`=1013 THEN "Austin"

  ELSE WHEN `lookup_code`=1025 or `lookup_code`=1017 or `lookup_code`=1005 THEN "Orlando"

  ELSE WHEN `lookup_code`=1021 or `lookup_code`=1018 or `lookup_code`=1015 or `lookup_code`=1011 THEN "Tampa"

ELSE WHEN `lookup_code`=1030 THEN "Miami"

  ELSE "New Property Please Update"

  END

Or is this a alternative why to set it up

Case when `lookup_code`= 1026 THEN "Charlotte"

               CASE `lookup_code`= 1024 THEN "Charlotte"

               CASE `lookup_code`=1020 or `lookup_code`= 1009 or `lookup_code`=1023 THEN "Raleigh/Durham"

   CASE `lookup_code`=1019 or `lookup_code`= 1010 or `lookup_code`= 1016 or `lookup_code`=3001 THEN "Dallas"

               CASE `lookup_code`=1014 or `lookup_code`=1022 or `lookup_code`=1006 or `lookup_code`=1013 THEN "Austin"

   CASE `lookup_code`=1025 or `lookup_code`=1017 or `lookup_code`=1005 THEN "Orlando"

   CASE `lookup_code`=1021 or `lookup_code`=1018 or `lookup_code`=1015 or `lookup_code`=1011 THEN "Tampa"

               CASE `lookup_code`=1030 THEN "Miami"

   ELSE THEN "New Property Please Update"

   END


I could use SQL to split them up but it seems redundant to split them and then join the data sets.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Instead of using ELSE WHEN just use WHEN for additional cases. Use just ELSE for your default clause.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Instead of using ELSE WHEN just use WHEN for additional cases. Use just ELSE for your default clause.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • mhouk
    mhouk Member

    thanks its always the simpler dropped text that I have problems with.

  • MarkSnodgrass
    MarkSnodgrass Coach

    @mhouk to further simplify your case statement, you can use the IN clause so that you have just one line per city. Something like this:

    Case WHEN `lookup_code` IN (1024,1026) THEN 'Charlotte'
WHEN `lookup_code` IN (1009,1020,1023) THEN 'Raleigh/Durham'
WHEN `lookup_code` IN (1010,1016,1019,3001) THEN 'Dallas'
WHEN `lookup_code` IN (1006,1013,1014,1022) THEN 'Austin'
WHEN `lookup_code` IN (1005,1017,1025) THEN 'Orlando'
WHEN `lookup_code` IN (1011,1015,1018,1021) THEN 'Tampa'
WHEN `lookup_code` = 1030 THEN 'Miami'
ELSE 'New Property Please Update'
END


    You can use the IN statement one line and equals on another line as shown above since you only had one entry for Miami. The end result is a much more concise case statement.

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • mhouk
    mhouk Member

    thanks for your input! I didn't think you could use an array of numbers for the In function I thought you had to use another column.

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited August 2022

    I did a quick test before posting and it worked. It's assuming your lookup_code column is numeric. If it is a string column, you would need to put single quotes around each value like this: ('1000','1001')

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    Alternatively you could store three values in a lookup table dataset and use an Etl to left join it to your original data. Then use a formula tile to do a coalesce to set the default value (your else clause) so you keep your lookup list in a single place rather than possibly across multiple beast modes.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories