I have a table of Clickstream data I'm pulling in via SQL. One column is, itself, a string of concatenated values for products. For instance, values may be:
| Order | Products |
|---|
| 1 | blue shoes, red shoes |
| 2 | red shirt, blue shirt, green shirt |
| 3 | brown boots |
| 4 | red shoes, blue shirt |
I originally tried to group these with a Wildmatch in a calculated dimension:
=if(wildmatch(Products, '*shoes*','*boots*'), 'Shoes', if(wildmatch(Products, '*shirt*'), 'Shirts'))
This essentially creates a new dimension that aligns as follows:
| Order | Products | Category |
|---|
| 1 | blue shoes, red shoes | shoes |
| 2 | red shirt, blue shirt, green shirt | shirt |
| 3 | brown boots | shoes |
| 4 | red shoes, blue shirt | shoes |
This tags the Products with preference to the first if() clause in my expression. So if I create a drill down as Category > Products and look for "Shirts" in my categories, I won't see the "Blue Shirt" from Order 4.
Is there any approach to help me put dimensions into multiple groups, such that Order 4 would show for both Shoes and Shirts?
In reality, I have thousands of rows with many products that fall into different categories, in no particular order or predictable pattern, so I'm not sure if a mapping table would be able to predict all possible combinations. There might be 10 products in one string that apply to 5 different categories.
