4 Replies Latest reply: Apr 5, 2018 9:59 AM by Sasidhar Parupudi RSS

    Looking up data from another table

    Paul Evans

      Hello, I'm new to Qlik Sense.


      I'd like to be able to add a column to my main data table by looking up data from a secondary data table based on 2 criteria columns which I've already associated between the 2 tables. 


      An example of my main data table can be seen below:

         

      DateNameColourSizeQuantityScore
      01/04/2018FredGreen1cm502
      01/04/2018JulieRed2cm1003
      01/04/2018PeterRed3cm756
      01/04/2018JohnBlue1cm252
      01/04/2018SamGreen3cm508
      01/04/2018PeterBlue1cm1002
      02/04/2018JohnGreen2cm755
      02/04/2018SamBlue3cm257
      02/04/2018FredGreen1cm1002
      02/04/2018JulieRed2cm753
      02/04/2018PeterGreen3cm258
      02/04/2018PeterRed1cm501
      02/04/2018JohnGreen3cm1008
      02/04/2018SamGreen1cm1002
      02/04/2018FredRed2cm753
      02/04/2018JulieGreen3cm258
      03/04/2018JohnBlue1cm1002
      03/04/2018SamRed1cm751
      03/04/2018PeterBlue2cm1004
      03/04/2018JohnBlue2cm754
      03/04/2018SamRed1cm251
      03/04/2018FredBlue3cm507
      03/04/2018JulieGreen1cm1002
      03/04/2018PeterBlue1cm752

       

       

      The columns 'Colour' & 'Size' are my 2 criteria columns and the 'score' column is the new column I'd like to add where the value is automatically looked up from the 2nd table as shown below:

       

      ColourSizeScore
      Red1cm1
      Red2cm3
      Red3cm6
      Blue1cm2
      Blue2cm4
      Blue3cm7
      Green1cm2
      Green2cm5
      Green3cm8

       

      Can anybody please help with this?


      Thank in advance!

        • Re: Looking up data from another table
          praveen A

          you mean that based on second table color & size ,you want to re-write the score in main table ???

           

          can you give us ,,the bit more clarification ??

           

          or else can you provide us the sample o/p ?

          • Re: Looking up data from another table
            Sunny Talwar

            May be using ApplyMap and Mapping load

             

            MappingTable:

            Mapping

            LOAD Colour&'|'&Size,

            Score;

            LOAD * INLINE [

                Colour, Size, Score

                Red, 1cm, 1

                Red, 2cm, 3

                Red, 3cm, 6

                Blue, 1cm, 2

                Blue, 2cm, 4

                Blue, 3cm, 7

                Green, 1cm, 2

                Green, 2cm, 5

                Green, 3cm, 8

            ];


            Table:

            LOAD *,

            ApplyMap('MappingTable', Colour&'|'&Size, Null()) as Score;

            LOAD * INLINE [

                Date, Name, Colour, Size, Quantity

                01/04/2018, Fred, Green, 1cm, 50

                01/04/2018, Julie, Red, 2cm, 100

                01/04/2018, Peter, Red, 3cm, 75

                01/04/2018, John, Blue, 1cm, 25

                01/04/2018, Sam, Green, 3cm, 50

                01/04/2018, Peter, Blue, 1cm, 100

                02/04/2018, John, Green, 2cm, 75

                02/04/2018, Sam, Blue, 3cm, 25

                02/04/2018, Fred, Green, 1cm, 100

                02/04/2018, Julie, Red, 2cm, 75

                02/04/2018, Peter, Green, 3cm, 25

                02/04/2018, Peter, Red, 1cm, 50

                02/04/2018, John, Green, 3cm, 100

                02/04/2018, Sam, Green, 1cm, 100

                02/04/2018, Fred, Red, 2cm, 75

                02/04/2018, Julie, Green, 3cm, 25

                03/04/2018, John, Blue, 1cm, 100

                03/04/2018, Sam, Red, 1cm, 75

                03/04/2018, Peter, Blue, 2cm, 100

                03/04/2018, John, Blue, 2cm, 75

                03/04/2018, Sam, Red, 1cm, 25

                03/04/2018, Fred, Blue, 3cm, 50

                03/04/2018, Julie, Green, 1cm, 100

                03/04/2018, Peter, Blue, 1cm, 75

            ];

            • Re: Looking up data from another table
              Sasidhar Parupudi

              Another option is to simply do a left join to the main table

              MainTable:

               

               

              LOAD * INLINE [

               

               

                  Date, Name, Colour, Size, Quantity

               

               

                  01/04/2018, Fred, Green, 1cm, 50

               

               

                  01/04/2018, Julie, Red, 2cm, 100

               

               

                  01/04/2018, Peter, Red, 3cm, 75

               

               

                  01/04/2018, John, Blue, 1cm, 25

               

               

                  01/04/2018, Sam, Green, 3cm, 50

               

               

                  01/04/2018, Peter, Blue, 1cm, 100

               

               

                  02/04/2018, John, Green, 2cm, 75

               

               

                  02/04/2018, Sam, Blue, 3cm, 25

               

               

                  02/04/2018, Fred, Green, 1cm, 100

               

               

                  02/04/2018, Julie, Red, 2cm, 75

               

               

                  02/04/2018, Peter, Green, 3cm, 25

               

               

                  02/04/2018, Peter, Red, 1cm, 50

               

               

                  02/04/2018, John, Green, 3cm, 100

               

               

                  02/04/2018, Sam, Green, 1cm, 100

               

               

                  02/04/2018, Fred, Red, 2cm, 75

               

               

                  02/04/2018, Julie, Green, 3cm, 25

               

               

                  03/04/2018, John, Blue, 1cm, 100

               

               

                  03/04/2018, Sam, Red, 1cm, 75

               

               

                  03/04/2018, Peter, Blue, 2cm, 100

               

               

                  03/04/2018, John, Blue, 2cm, 75

               

               

                  03/04/2018, Sam, Red, 1cm, 25

               

               

                  03/04/2018, Fred, Blue, 3cm, 50

               

               

                  03/04/2018, Julie, Green, 1cm, 100

               

               

                  03/04/2018, Peter, Blue, 1cm, 75

               

               

              ];

              Left Join(MainTable)

              Score:

               

               

              LOAD * INLINE [

               

               

                  Colour, Size, Score

               

               

                  Red, 1cm, 1

               

               

                  Red, 2cm, 3

               

               

                  Red, 3cm, 6

               

               

                  Blue, 1cm, 2

               

               

                  Blue, 2cm, 4

               

               

                  Blue, 3cm, 7

               

               

                  Green, 1cm, 2

               

               

                  Green, 2cm, 5

               

               

                  Green, 3cm, 8

               

               

              ];