3 Replies Latest reply: Nov 17, 2016 12:47 AM by Shraddha Gajare RSS

    Calculated Fields across multiple tables in Qlik Sense Data Load Editor

    Giles Walker

      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