topic Possible To Place Dimensions Into Multiple Groups? in App Development

Possible To Place Dimensions Into Multiple Groups?

Anonymous — Tue, 10 Apr 2018 18:40:23 GMT

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.

Re: Possible To Place Dimensions Into Multiple Groups?

sunny_talwar — Tue, 10 Apr 2018 18:53:59 GMT

Try like this

Table:

LOAD * INLINE [

Order, Products

1, "blue shoes, red shoes"

2, "red shirt, blue shirt, green shirt"

3, brown boots

4, "red shoes, blue shirt"

];

LinkTable:

LOAD DISTINCT Order,

Category

Where Len(Trim(Category)) > 0;

LOAD Order,

if(wildmatch(SubField(Products, ','), '*shoes*','*boots*'), 'Shoes',

if(wildmatch(SubField(Products, ','), '*shirt*'), 'Shirts')) as Category

Resident Table;

Re: Possible To Place Dimensions Into Multiple Groups?

Anonymous — Tue, 10 Apr 2018 20:19:55 GMT

This is perfect. Thanks!