2 Replies Latest reply: Feb 20, 2015 2:39 PM by Ryan Liljestrand RSS

    load compare two fields

    Ryan Liljestrand

      I have a SQL table that contains two fields: name (primary key), and last_update.  I also have a QVD containing the same data, plus some flags for further processing; this QVD gets read back in on subsequent runs.  For example:

       

      QVD

      namelast_update
      A1
      B1
      C1
      D1

       

      Server

      namelast_update
      A1
      B2
      C1
      D3
      E2
      F3

       

      I am trying to do a LOAD where the result will be a table:

       

      QVD_updates (i.e. only names in QVD where Server last update is newer than QVD last update).

      namelast_update
      B2
      D3

       

      How is this done?

       

      Thanks!

        • Re: load compare two fields
          Manish Kachhia

          First while Storing your QVD add one more field like below

          QVD:

          Load *, name&last_update as key1 Inline

          [

            name, last_update

            A, 1

            B, 1

            C, 1

            D, 1

          ];

           

           

          STORE QVD into QVD.qvd(QVD);

          DROP TABLE QVD;

           

          Now

          Use as below...

           

          temp:

          LOAD

              key1

          FROM

          [D:\QlikView\Community\QVD.qvd](qvd);

           

           

          temp2:

          Load COUNT(DISTINCT key1) as TotalKey1 Resident temp;

           

           

          Let vTotalKey1 = NUM(PEEK('TotalKey1',0,'temp2'));

           

           

          For i = 0 to $(vTotalKey1)-1

           

           

            Let vName = PEEK('key1',$(i),'temp');

            Let V1 = Left('$(vName)',1);

            Let V2 = Right('$(vName)',1);

           

            Server:

            Load *,Left('$(Name)',1) Inline

            [

            name, last_update

            A, 1

            B, 2

            C, 1

            D, 3

            E, 2

            F, 3

            ] Where name = '$(V1)' and last_update > '$(V2)';

           

           

          Next;

           

           

          Drop Table temp;

          Drop Table temp2;