Magic ETL Nested Case Statement

user027926

Hi all,

I am trying to create a nested case statement (I 've provided an over simplified one that I've been experimenting with, but I have the same issue):

(CASE 

 WHEN `Specific Category`LIKE '%People%' THEN 'People' 

 WHEN `Specific Category`LIKE '%Unknown%' THEN 

 (CASE 

 WHEN `Platform` LIKE '%Amazon%' THEN 'Amazon Likely Retail'

 ELSE 'blah blah'

 END)

 ELSE 'would be specific cat' 

 END)

 

It continued to not acknowledge the nested Case statement and all of the resulting values are either 'People' or 'would be specific cat'

Any ideas what's going on here? I checked and all of the values do exist. TIA

MarkSnodgrass

In your specific category column is Unknown entered exactly like that? Meaning does it have a capital U? These can be case sensitive and it can often be easier to wrap an UPPER() function around your specific category field and then have your like statements be all uppercase. Your Then or Else statement does not need to be uppercase, so you can still have the resulting output however you like.

user027926

Yes, I was certain that it is exactly like that-- did a Text edit as the tile before that and chose first letter Capital. I'll make it all UPPER and see if it helps. Thanks

user027926

Thanks @MarkSnodgrass -- still didn't work (I changed the Text Formatting tile to be all UPPER instead of first letter cap)

MarkSnodgrass

I forget the text formatting tile is there. I tend to just do it all in the formula tile like this:

(CASE 

 WHEN UPPER(`Specific Category`) LIKE '%PEOPLE%' THEN 'People' 

 WHEN UPPER(`Specific Category`) LIKE '%UNKNOWN%' THEN 

 (CASE 

 WHEN `Platform` LIKE '%Amazon%' THEN 'Amazon Likely Retail'

 ELSE 'blah blah'

 END)

 ELSE 'would be specific cat' 

 END)

Either way, if it is converting specific category to uppercase, and your like statement is now uppercase, it should be good. The only caveat is if the categories with the phrase unknown also contain the phrase people. Since people is evaluated first, there would be no unknown entries left.

Also, I assume you are running the full ETL and not just relying on the preview window because that will only give you the first 100 entries and your unknown entries might not be in those rows.

user027926

Thanks @MarkSnodgrass -- in the end, it was, as you had suggested a spelling error (which was not in my formula but was being outputted from the CRM automatically). Thank you!!

MarkSnodgrass

That's great to hear @user027926 . If you can mark any answers as accepted that helped you, that would help others in the community.