Case Syntax

mhouk

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.

GrantSmith

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

GrantSmith

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

mhouk

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

MarkSnodgrass

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

mhouk

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

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')

GrantSmith

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.