Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Gysbert -
This is awesome! So much better than trying to build it into equations. Thanks so much!
Steve