Identify Multiple Different Matches in Same Column

user060919
user060919 Member
in Magic ETL

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

  • ST_Superman
    ST_Superman Domo Employee

    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

Categories