Identify Multiple Different Matches in Same Column

Hello,
I'm looking to be able to build cards that compare different products that are tied to different opportunities. The data currently comes in via one column, and if multiple products are on the opportunity, it is comma-deliminated.
OPPNUM Products
12345 Product1,Product2,Product3
I've tried doing via Beast Mode:
CASE WHEN 'Products' LIKE '%Product1%' = 'Product1'
WHEN 'Products' LIKE '%Product2%' = 'Product2'
etc.
end
However if it matches on Product1, that entire Opp is then tied to Product1 and it doesn't seem to check for Product2'. I'm fine if the same opportunity becomes listed multiple times for each product, but it doesn't do that, it just assigns Product1 and forgets about the rest.
Looking for any help.
Thanks!
Comments
-
I would recommend using magic ETL to first split your product column and then Collapse Columns to convert those column values into rows.
The output you would be looking for would look more like this:
OPPNUM Products
12345 Product1
12345 Product2
12345 Product3
22222 Product1
...If you need to keep this in a beastmode calculation, you will need to write a separate beastmode for each product.
The way the Case statement gets evaluated is that it looks for the first condition that is true and then stops evaluating. So in your example, if Product 1 was listed, then it would never look for any other products.
You would need to write a beastmode for each product.
Product1:
CASE WHEN `Products` LIKE '%Product1%' then 'Product1' END
Product2:
CASE WHEN `Products` LIKE '%Product2%' then 'Product2' END
Product 3:
CASE WHEN `Products` LIKE '%Product3%' then 'Product3' END1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 651 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 795 Beast Mode
- 78 App Studio
- 44 Variables
- 755 Automate
- 187 Apps
- 480 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive