7 Replies Latest reply: May 14, 2016 11:04 AM by Gysbert Wassenaar RSS

    issue with EXISTS, appending data to a table

    Boris Tyukin

      hi Qlikkers,

       

      I am obviously missing something simple here but I cannot figure it why this does not work. I created a script below to demonstrate the issue. I have one main table called enc which has unique key encid for that table. I also created a copy of that key as enc2.

       

      Then I have another table called ord, which has multiple rows per encid. I need to do an append to ord table (this will be used for incremental load process).

       

      I expect ord table populated like that if the script runs properly:

       

      orderid,encid,ordercol1

      1,1,old1

      2,1,old2

      3,1,old3

      4,2,new4

      5,2,new5

       

      but I get this:

       

      encid enccol1 orderid ordercol1
      1enc1
      2enc24new4
      2enc25new5


      what I am missing here?

       

      enc:
      load
      *,
      encid as encid2
      Inline [
      encid,enccol1
      1,enc1
      2,enc2
      ]
      ;

      //new data
      ord:
      load * Inline [
      orderid,encid,ordercol1
      4,2,new4
      5,2,new5
      ]
      ;


      // old data
      CONCATENATE (ord)
      load *
      WHERE
      not EXISTS(encid2,encid)
      ;
      load * Inline [
      orderid,encid,ordercol1
      1,1,old1
      2,1,old2
      3,1,old3
      4,2,old4
      5,2,old5
      ]

      ;