Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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);