How to calculate fields across different tables in load script

[Anonymous]

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!

[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);