0 Replies Latest reply: Feb 20, 2018 3:46 AM by Aravind Manjunath RSS

    Using Dynamic columns in Lookup() function

    Aravind Manjunath

      Hi All,

      I'm a newbie to Qlik environment and am using Qliksense.

       

      I have a requirement wherein I need to lookup values in each column from a table with multiple columns. I tried having the load statement inside a FOR loop and using variables as shown in the code below:

       

      The columns (fields) in the table "temp" are stored in the field 'concat1' in the table "test2". The columns to be used for mapping are "App Seg3" in table "temp" and "Application 5" in table "test2" (both of which have same values).

      The tables are somewhat like this

       

      Test2:

       

      Concat1Application 5
      aaa1
      bbb2
      ccc3

       

      Temp:

       

      App Seg 3aaabbb
      1xxxxwwww
      2yyyyvvvvv
      3zzzzuuuu

       

       

      I want to add new-column field in  test 3 with the output looking as below

       

      Concat1Application 5new-column
      aaa1xxxx
      bbb2vvvv
      aaa2yyyy

       

       

       

      The sample code I've written to start with. This is not getting executed when I click "Load Data" button in data editor. Also, I think, I'm reloading test2 multiple times which should not be done. Can you please suggest on how to do this?

       

       

      Let vNoOfRows = NoOfRows('concat1');

      test3:

       

      FOR i=0 to $(vNoOfRows)-1

        SET vRecordID = fieldvalue('concat1',$(i));

       

          LOAD

                    *,

                   

          Lookup('$vRecordID','"App Seg3"',"Application 5",'temp')AS Auto_T1

      RESIDENT test2;

      DROP table test2;

      Next i;

       

      Thanks a lot!

      Regards,
      Aravind (dudeinred1)