5 Replies Latest reply: Aug 21, 2017 9:27 AM by Michael Solomovich 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

              Amazing!

              Thank you, Gysbert, that worked!

              • Re: Load max(ID) from QVD
                zahid Rahim

                Can you please examine my code i am getting below error:

                 

                Connector reply error: ErrorSource: OraOLEDB, ErrorMsg: ORA-00936: missing expression

                 

                 

                On my below mentioned code after the line: /////////////////////////////////////////////// Load Incremental Data

                 

                ---------------------------------------------------------------------------------------------------------

                 

                TempTable:

                LOAD MAX(LAST_UPDATE_DATE) AS LUD

                FROM  [Lib://LIB_QVD/qlik_inc_load.QVD]  (qvd);

                 

                LET MAX_LUD = FieldValue('LUD') ;

                 

                /////////////////////////////////////////////// Load Incremental Data

                [QV_Table]:

                SQL SELECT Id,

                    TrDate,

                    Name,

                    Amount ,

                    LAST_UPDATE_DATE

                FROM apps.qlik_inc_load

                WHERE last_update_date > $(MAX_LUD);

                 

                 

                STORE QV_Table INTO [Lib://LIB_QVD/qlik_inc_load.QVD] (qvd);

                 

                DROP Table TempTable;

                 

                ---------------------------------------------------------------------------------------------------------

                  • Re: Load max(ID) from QVD
                    Michael Solomovich

                    Hi Zahid,

                     

                    First of all, it is not a good idea to append your question to an existing discussion, especially if it is closed.  You better open your own.

                     

                    Second, the fieldvalue() requires two parameters, for example:

                    LET MAX_LUD = FieldValue('LUD',1) ;

                    I would rather use peek() here:

                    LET MAX_LUD = peek('LUD') ;


                    Third, you cannot use this syntax in Oracle:

                    WHERE last_update_date > $(MAX_LUD);


                    Take a look here, part 2:

                    Some nuances working with Oracle


                    In any case, open your own discussion, if you want somebody else to notice it.


                    Regards,

                    Michael

                • 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;