4 Replies Latest reply: Dec 20, 2017 3:20 PM by Erik Hughes RSS

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

    Erik Hughes

      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

        • Re: Design question around filtering by a set of possibilites over multiple fields
          Andy Weir

          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

            • Re: Design question around filtering by a set of possibilites over multiple fields
              Erik Hughes

              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?

                • Re: Design question around filtering by a set of possibilites over multiple fields
                  Andy Weir

                  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
                  KEYVALUE
                  1Color1Red
                  1Color2Red
                  2Color1Red
                  2Color2Green
                  3Color1Blue
                  3Color2Blue
                  4Color1Red
                  4Color2Blue

                   

                  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

                    • Re: Design question around filtering by a set of possibilites over multiple fields
                      Erik Hughes

                      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;