Thanks for the response Andy, but Im having trouble understanding it.
Do you mean having color1 and color2 as keys to a keytable?
Do you mean a composite of the two fields string concatenated and with each pair having a key?
Currently I'm making a color dimension table in the load script via:
Color1 as Colors
Color2 as Colors
And now my understanding is I need to build keys in the original fact table and a keytable to relate them to the Colors dimension. Im hoping there is a way for qlik sense to do this for me?
It should look something like this, obivously youll have more fields this is just what you need to distingush the 2 sets in the same table.
ID KEY VALUE 1 Color1 Red 1 Color2 Red 2 Color1 Red 2 Color2 Green 3 Color1 Blue 3 Color2 Blue 4 Color1 Red 4 Color2 Blue
So in your app you can filter on VALUE = red and it will return the rows that a red and you have the flexibility to filter your date further using the key that identifies your sort either in your expression using set analysis or with a filter.
Andy, thanks for explaining. You were a huge help!
I used a composite key and a crosstable load to achieve the structure you showed. Now I can filter off of ColorFieldValue='Red' and it shows all records that have "Red" in either of the fields. Also, can further qualify with a filter on ColorFieldKey.
Load script below
Item &'-'&" Color1 "&'-'& Color2 as CompositeKey
(ooxml, embedded labels, table is facts_table);
load CompositeKey, Color1 resident facts_table;
load CompositeKey, Color2 resident facts_table;