3 Replies Latest reply: Apr 7, 2015 8:44 AM by Linoy Elias RSS

    Load max(ID) from QVD

    Linoy Elias

      Hi all,

       

      I'm doing an incremental load from QVD file and add data from database based on ID field.

      The thing is that my QVD file contains 4 different parts of data - lets's say a, b, c,and d, and each part has it's own IDs.

      So I have to find max(ID) for every part and then add new rows from database.

      The problem is that finding max(id) from QVD file takes forever!

      Please help me to improve the script. Thank you!

      Here it is:


      //Load data from QVD:

      MyTable:

        LOAD * FROM "$(vDataPath)\MyFile.qvd" (qvd);

       

      //Find max(IDs): --> IT STUCKS HERE!!!

      temp1:

        LOAD max(ID) as MaxID1 Resident MyTable where Type='a';

      temp2:

        LOAD max(ID) as MaxID2 Resident MyTable where Type='b';

      temp3:

        LOAD max(ID) as MaxID3 Resident MyTable where Type='c';

      temp4:

        LOAD max(ID) as MaxID4 Resident MyTable where Type='d';

       

      //Put max(IDs) in variables:

      LET MaxID1= Peek('MaxID1 ',-1,'temp1');

      LET MaxID2= Peek('MaxID2',-1,'temp2');

      LET MaxID3 = Peek('MaxID3',-1,'temp3');

      LET MaxID4 = Peek('MaxID4',-1,'temp4');


      //Load new data from database:

      Concatenate(MyTable)

      LOAD *;

      SQL SELECT *

      FROM MyTableInDB

      where Type='a'

      and ID > $(MaxID1 )

       

      Concatenate(MyTable)

      LOAD *;

      SQL SELECT *

      FROM MyTableInDB

      where Type='b'

      and ID > $(MaxID2 )

       

      Concatenate(MyTable)

      LOAD *;

      SQL SELECT *

      FROM MyTableInDB

      where Type='c'

      and ID > $(MaxID3 )

       

      Concatenate(MyTable)

      LOAD *;

      SQL SELECT *

      FROM MyTableInDB

      where Type='d'

      and ID > $(MaxID4 )

        • Re: Load max(ID) from QVD
          Gysbert Wassenaar

          You're not loading the max values from the qvd, but from a resident table.

           

          Try this:

           

          MyTable:

          LOAD Type as MaxType, max(ID) as MaxID

          FROM "$(vDataPath)\MyFile.qvd" (qvd)

          GROUP BY Type;

           

          LET MaxID1 = fieldvalue('MaxID',fieldindex('MaxType','a')) ;

          LET MaxID2 = fieldvalue('MaxID',fieldindex('MaxType','b')) ;

          LET MaxID3 = fieldvalue('MaxID',fieldindex('MaxType','c')) ;

          LET MaxID4 = fieldvalue('MaxID',fieldindex('MaxType','d')) ;

           

          DROP TABLE MyTable ;

          • Re: Load max(ID) from QVD
            Linoy Elias

            There is another solution, a bit longer but works fast as well:

             

            //Find max(ID) for Type='a':

             

            TempTable:

            LOAD ID as ID1 FROM "$(vDataPath)\MyFile.qvd" (qvd) where Type='a';

             

            MaxID:

            LOAD

                  Max(ID_) as MaxID1;

            LOAD

                  FieldValue('ID1 ', IterNo()) as ID_

                  Autogenerate(1)

                  While not IsNull(FieldValue('ID1 ', IterNo()));

             

            Let vMaxID1 = Peek('MaxID1', -1, 'MaxID');

             

            DROP TABLES TempTable, MaxID;