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
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive