Calculated Fields across multiple tables in Qlik Sense Data Load Editor

[gileswalker]

Hi Qlik Community,

Hopefully someone can assist me. I am a newbie, struggling with data load editor in Qlik SENSE.  Here is my story......

I am loading order placement and order fulfilment data from 3 different source tables (via ODBC) and the retrieval of data is successful.

I am then attempting to create KPI measures by using the data, and for instance I will have to use fields from 2 of the tables to create a calculation such as ordered_date (from table A) plus standard_lead_time_days (from table B) giving me a new calculated date value of target_dispatch_date. 

Then I will need to use the actual_date_dispatched (from table C) and calculate if that exceeds the target_dispatch_date, to result in a hit or miss. That kind of thing.  There will then be further calculations afterwards like days_over_target_dispatch to report extra details about 'misses'.

I am not sure how to do all this exactly.  When you are operating with just 1 table, the calculations are easy, but across different tables, it seems to work differently from what I can work out.

I trawled the community and found what I thought was the right thing, RESIDENT function.

So.....I have spent a half day trying to get Resident function to work, which it did for the table A to table B calculation (although I was a bit shaky knowing which fields to load ahead of the calculation line, resulting in synthetic joins), but it only worked for that single calculation.  The minute I then tried to use the newly created date measure of target_dispatch_date in the next calculation Qlik says "Field not found'.

Without pasting my entire code here, is RESIDENT function the right means to do this, or have I missed something standard during my 'teach-yourself-Qlik' training?  I am sure this type of calculation methodology is a norm using Qlik, but I have got stuck and I'm not sure which way to go.  I must say that I do struggle with getting my head around the workings of the Resident function although I somehow got it to work in part.

As a 10 year MS Access user it's humbling starting to learn a new piece of software like Qlik, so I entirely appreciate help that the community can bring.

Yours with hope.........Giles

[jonathandienst]

You will have to combine the 3 tables by joining or mapping in the relevant information from table B and C into table A (ApplyMap()).

Then the calculation becomes straight forward. with a few exceptions, such as Exists and Lookup,LOAD statements can only reference fields from one table at a time. Presumably you have some common key to associate the records in one tables with the corresponding records in the other tables.

[gileswalker]

Hi Jonathan

Thanks for coming back to me.  So my tables are joined thus:

Table A

Left Join

Table B

Left Join

Table C

Table A is the primary source of data, and Tables B and C are supplementary data fields.

I have 4 fields that are mirrored across each table (site number, customer number, order number, order release number), allowing Qlik to create its own unique key, and so the records align properly when I pull data together into the visuals.

Given this additional information, do you think ApplyMap is the right way to go? I will be honest I hadn't heard of it until you mentioned it.

Was I on the wrong track with Resident function?

Thanks for helping so quickly!  Cheers. Giles

[shraddha_g · Accepted solution]

applymap is preferred over Left join