Magic ETL Nested Case Statement
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
-
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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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
0 -
Thanks @MarkSnodgrass -- still didn't work (I changed the Text Formatting tile to be all UPPER instead of first letter cap)
0 -
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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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!!
1 -
That's great to hear @user027926 . If you can mark any answers as accepted that helped you, that would help others in the community.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- 10.7K All Categories
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 101 Apps
- 705 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 23 お知らせ
- 63 Kowaza
- 297 仲間に相談
- 649 ひらめき共有