Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

win_pratt
New Contributor

Possible To Place Dimensions Into Multiple Groups?

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Possible To Place Dimensions Into Multiple Groups?

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;

2 Replies
MVP
MVP

Re: Possible To Place Dimensions Into Multiple Groups?

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;

win_pratt
New Contributor

Re: Possible To Place Dimensions Into Multiple Groups?

This is perfect. Thanks!