1 Reply Latest reply: Sep 5, 2016 8:14 AM by Gysbert Wassenaar RSS

    How to calculate fields across different tables in load script

    jade wind

      Hi everyone,

       

      I know I can calculate fields within a table in the LOAD script but cannot work out how to do it across different tables. Below is an example of the tables:

       

      Table1:

      LOAD

          "ID",

          "Year",

      FROM [lib://RFA385.2/RFA385.2.WCC.xlsx]

      (ooxml, embedded labels, table is page);

       

       

      Table2:

      LOAD

          "ID",

          "Hours",

          "Amount Paid"

      FROM [lib://RFA385.2/RFA385.2.WCP.xlsx]

      (ooxml, embedded labels, table is page);

       

       

      Table3:

      LOAD

          "Rate",

          "Year"

      FROM [lib://RFA385.2/Hourly rates.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      Table 1 and Table 2 will be linked by ID field, and Table 1 and Table 3 will be linked by Year field. I need to multiply "Hours" and "Rate" for the same "Year" and compare with "Amount Paid". If the calculation is more than "Amount Paid" then a new field needs to show this record as "No", otherwise "Yes".

       

      This can be easily done in Excel but can anyone tell me how I can do it in Qlik Sense?

       

      Many thanks in advance!

        • Re: How to calculate fields across different tables in load script
          Gysbert Wassenaar

          Perhaps like this:

           

          mapTable1:

          MAPPING LOAD

              "ID",

              "Year",

          FROM [lib://RFA385.2/RFA385.2.WCC.xlsx]

          (ooxml, embedded labels, table is page);

           

           

          mapTable3:

          MAPPING LOAD

              "Rate",

              "Year"

          FROM [lib://RFA385.2/Hourly rates.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Table2:

          LOAD

              "ID",

              "Hours",

              "Amount Paid",

               If( ApplyMap('mapTable3', ApplyMap('mapTable1',ID)) > "Amount Paid", 'No', 'Yes') as "New Field"

          FROM [lib://RFA385.2/RFA385.2.WCP.xlsx]

          (ooxml, embedded labels, table is page);