6 Replies Latest reply: May 4, 2018 5:29 AM by Frank Schoch RSS

    incremental load in script

    Frank Schoch

      Hello dear community,
      I would like to load my spreadsheets only incrementally. So only the records that have been added since the last loading.
      For this, I have built the following sample conflict in the data load script.

      LIB CONNECT TO 'host.de (user)';
      [Table 1]:
      LOAD
         
      [id] AS ID,
         
      [key_id] AS Key_id,
         
      [caseid] AS Case_id,
         
      [text] AS text;
      SQL SELECT
         
      id,
         
      key_id,
         
      CaseID,
         
      text
      FROM `Server`.`Table1`
      WHERE id> '$ (vMaxID_Table1)';

      [MaxiMalWertTab]:
      Load max (Table1.id) AS MaxiMalWert resident Table1;
      Let vMaxID_Table1 = Peek ('MaxiMalWert');
      drop table MaxiMalWertTab;

      CONCATENATE LOAD
      [ID]
      [KEY_ID]
      [Case_d]
      [Text]
      FROM [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd)
      where not Exists(ID)
      ;

      store [table1] into [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd);


      I built this script following the tutorial on help.qlik.com, but it does not seem to work. Can any of you please help me?
      VG

        • Re: incremental load in script
          Nick Hoekstra

          This part should be above [Table1] load statement, and should reference the QVD as you're trying to get the newer data from SQL using the where clause based on the max id that is in the qvd:

           

          [MaxiMalWertTab]:
          Load max (Table1.id) AS MaxiMalWert resident Table1;
          Let vMaxID_Table1 = Peek ('MaxiMalWert');
          drop table MaxiMalWertTab;


          Regards,

           

          Nick

            • Re: incremental load in script
              Frank Schoch

              what should it change?
              Otherwise, I always determine at runtime, which was the last ID (the highest) and notice the variables in the variable.
              What else does not happen with your idea? or?
              VG

                • Re: incremental load in script
                  Nick Hoekstra

                  Your script should look something like this:

                   

                  //-----------Start

                  // First check what the latest ID is from the data you already have

                  MaxValue:
                  Load

                      max (ID) AS MaxiMalWert

                  FROM [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd);


                  //Store the max ID value in a variable
                  Let vMaxID = Peek ('MaxiMalWert',0,'MaxiMalWertTab');


                  //Drop table that was needed to find the max ID value
                  drop table MaxValue;

                   

                  //open connection to a server

                  LIB CONNECT TO 'host.de (user)';

                   

                  //Fetch data from your database where the ID is greater than the value that is stored in vMaxID.

                  [Table_1]:
                  LOAD
                     
                  [id] AS [ID],
                     
                  [key_id] AS [KEY_ID],
                     
                  [CaseID] AS [Case_d],
                     
                  [text] AS [Text];
                  SQL SELECT
                     
                  id,
                     
                  key_id,
                     
                  CaseID,
                     
                  text
                  FROM `Server`.`Table1`
                  WHERE id> '$ (vMaxID)';


                  //Concat the old data from the QVD to the newly fetch data to create one table.
                  CONCATENATE

                  LOAD
                       [ID]
                       [KEY_ID]
                       [Case_d]
                       [Text]
                  FROM [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd);


                  //Store the table with the new data in a QVD so further use.
                  store table1 into [lib://resources (qlik1_administrator)/KPI/DEV/table1.qvd] (qvd);

                  //-----------End



                  you might want to check the column names for easy readability and understanding.


                  Hope the comments help with understanding what is going on.