Bucketing different text fields if there is a match

I'm trying to count different text fields from a column called Reason and there are different reasons in that column. One row might have single or multiple reasons separated by a comma. Below are examples.
1) Reason 1
2) Reason 1, Reason 2, Reason 3
3) Reason 3, Reason 2
Beast mode using currently:
CASE
WHEN Reason
LIKE '%Reason 1%' THEN 'Reason 1'
WHEN Reason
LIKE '%Reason 2%' THEN 'Reason 2'
WHEN Reason
LIKE '%Reason 3%' THEN 'Reason 3'
ELSE 'Not Applicable'
END
This beast mode currently not counting the multiple reasons because it is just looking for a match and if there is match with one reason it is jumping to the next filed.
Current output:
Reason 1 - Count 2
Reason 2 - Count 0
Reason 3 - Count 1
Expected Output:
Reason 1 - Count 2
Reason 2 - Count 2
Reason 3 - Count 2
Answers
-
An easy way to get accurate counts of this is to break up your lists into individual items, with each item in its own row. You can do this dynamically in Magic ETL using this process:
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 305 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 106 SQL DataFlows
- 646 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 774 Beast Mode
- 75 App Studio
- 43 Variables
- 730 Automate
- 186 Apps
- 469 APIs & Domo Developer
- 61 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 403 Distribute
- 117 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 136 Manage
- 133 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive