8 Replies Latest reply: Sep 13, 2013 9:55 AM by Jan Dobrovolny RSS

    Calculated row in script after joining three tables

      Hello guys, I feel pretty stupid asking this question, I believe I´ve dealt with bigger problems in Qlikview so far, but I can´t seem to be able to solve this one.


      I have a field in a particular table, where I would like to add another row based on expression from values in table itself and from another table, which is joined by 3 ids away.
      I already have the table (table1, in which id_1 is also present) loaded through binary load and I basically take it, join ids from two different tables to it, then load it into new table (table1_updated), create new field based on ids I´ve joined (original_field_updated) and drop original table (table1) as well as joined ids in newly created one (table1_updated).
      The problem is, as soon as I drop these two ids, I loose the connection to the id_3 and 'new_value' doesn´t funcion as intended, taking only id_1 into account.
      I can´t leave those ids nor the table1 in, since then it causes syntetic joins with tables I joined ids from, obviously. Here is the script:

      LEFT JOIN (table1)
      LOAD    id_1,
      Resident table2;


      LEFT JOIN (table1)
      LOAD id_2,
      Resident table3;


      LOAD *,
      if(id_1 = x AND id_3 = y, 'new_value', original_field) as original_field_updated
      RESIDENT table1;

      DROP table table1;
      drop Fields id_2, id_3 from table1_updated;


      Does any of you know how to solve the issue? Thanks in advance for your time