Magic ETL Nested Case Statement

user027926
user027926 Member
in Magic ETL

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

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **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.
  • user027926
    user027926 Member

    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
    user027926 Member

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **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.
  • user027926
    user027926 Member

    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
    MarkSnodgrass Coach

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

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

Categories