2 Replies Latest reply: Apr 10, 2018 4:19 PM by Win Pratt RSS

    Possible To Place Dimensions Into Multiple Groups?

    Win Pratt

      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:

       

      OrderProducts
      1blue shoes, red shoes
      2red shirt, blue shirt, green shirt
      3brown boots
      4red 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:

       

      OrderProductsCategory
      1blue shoes, red shoesshoes
      2red shirt, blue shirt, green shirtshirt
      3brown bootsshoes
      4red shoes, blue shirtshoes

       

      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.