Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a transaction table in qlik sense that has two fields that have the same set of possible values...lets say Color1 and Color2. I need to make a dimension for filtering wherein when 'Red' is selected, the chart objects filter by where 'Red' is found in either field of the table. The set of possible colors is equal for both fields.
I have clumsily tried a keytable and a calculated dimension but cannot get the associations right. Please help
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.
Regards
Andy
Can you not restructure your table to concatenate the two fields into one fact table with a key value Identified as either color1 or color2
That will make your data easier to filter on.
Regards
Andy
Thanks for the response Andy, but Im having trouble understanding it.
Do you mean having color1 and color2 as keys to a keytable?
OR
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:
AllColors:
load Distinct
Color1 as Colors
resident facts_table;
Concatenate
load Distinct
Color2 as Colors
resident facts_table;
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.
Regards
Andy
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
LOAD
Item,
Color1,
Color2,
"Date",
Item &'-'&" Color1 "&'-'& Color2 as CompositeKey
FROM [...file]
(ooxml, embedded labels, table is facts_table);
Colors_CT:
Crosstable (ColorFieldKey,ColorFieldValue)
load CompositeKey, Color1 resident facts_table;
Crosstable (ColorFieldKey,(ColorFieldValue)
load CompositeKey, Color2 resident facts_table;