Design question around filtering by a set of possibilites over multiple fields

[hugheser27]

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

[ogster1974]

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

[hugheser27]

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?

[ogster1974 · Accepted solution]

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

[hugheser27]

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;