Wildcard Match in Set Analysis

[zagzebski]

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

[Gysbert_Wassenaar · Accepted solution]

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:

Record	Product
Record 1	Product A
Record 1	Product D
Record 2	Product B
Record 3	Product D
Record 4	Product C
Record 4	Product D
Record 4	Product E
Record 5	Product A
Record 5	Product D
Record 5	Product 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.

[zagzebski]

Gysbert -

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

Steve