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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any 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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive