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' END
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 555 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 568 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive