Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

zagzebski
Contributor

Wildcard Match in Set Analysis

He have a field that is basically the result of a multi - selection box and has several values in it separated by a " ; " it looks like the following:

Record1: Product A; Product D

Record2: Product B;

Record3 Product D;

Record4: Product C; Product D; Product E

Record5: Product A; Product D; Product E

We also have a dimensional table with our Products in it:

Product

Product A

Product B

Product C

Product D

Product E

I need to create an expression that will pull back any match from my multi selection field based on what I select from my Product Dimension.

For example if I selected Product D it would find a match in Record 1, 2 and 4. If I select Products A and B then I would find a match in records 1, 2, and 5 .

Thanks in advance for any help...Steve

1 Solution

Accepted Solutions

Re: Wildcard Match in Set Analysis

What I think you should do is transform the data in the script:

Records:

load Record, subfield(ProductList,';') as Product

from ...sourcetable...;

Replace Record and ProductList with the real names of the fields from your source table.

The load script above will split the Products list and create a record for each value. The result will be a table that looks like:

RecordProduct
Record 1Product A
Record 1Product D
Record 2Product B
Record 3Product D
Record 4Product C
Record 4Product D
Record 4Product E
Record 5Product A
Record 5Product D
Record 5Product E

This table will automatically be associated with the Products dimension table because both tables have a field named Product. You can now simply select a product in a listbox and the records will be filtered to show only those records with the selected product.


talk is cheap, supply exceeds demand
2 Replies

Re: Wildcard Match in Set Analysis

What I think you should do is transform the data in the script:

Records:

load Record, subfield(ProductList,';') as Product

from ...sourcetable...;

Replace Record and ProductList with the real names of the fields from your source table.

The load script above will split the Products list and create a record for each value. The result will be a table that looks like:

RecordProduct
Record 1Product A
Record 1Product D
Record 2Product B
Record 3Product D
Record 4Product C
Record 4Product D
Record 4Product E
Record 5Product A
Record 5Product D
Record 5Product E

This table will automatically be associated with the Products dimension table because both tables have a field named Product. You can now simply select a product in a listbox and the records will be filtered to show only those records with the selected product.


talk is cheap, supply exceeds demand
zagzebski
Contributor

Re: Wildcard Match in Set Analysis

Gysbert -

This is awesome! So much better than trying to build it into equations. Thanks so much!

Steve

Community Browser