1 Reply Latest reply: May 21, 2012 2:05 PM by Stefan Wühl RSS

    Incremental Load

      Hi All,


      At the moment, I am struggling to get my head around the incremental load process.... 


      I have a number of Excel templates that will be updated monthly (pricing) - these data should be inserted, updated or deleted into my master file.


      Attached is how I see the load process (but could be mistaken):


      File Structure.gif

      1) Excel files loaded into a QVW (7 tables loaded from each XL file) - for all new/amended data

      2) consolidated new/amended QVW file then creates 1 QVD file for each consolidated table.

      3) History is generated by creating 7 QVD tables from the previous consolidated file

      4) Incremental load process to combine both new and history.


      I have created both a primary key and a modification date (with ModificationTime currently set as "now()" in all QV docs (none created in XL yet).


      My code is as follows:





      let ThisExecTime = now();


      Load * from 2a_HISTORY_Pricing.qvd (qvd);

      where ModificationTime >= #$(LastExecTime)#

      and ModificationTime < #$(ThisExecTime)#;


      Load * from 1a_NEW_Pricing.qvd (qvd)

      where not exists (PrimaryKey);

      Inner join load PrimaryKey from 2a_HISTORY_Pricing.qvd (qvd);

      if scripterrorcount = 0 then

      store NewData into File.QVD;

      let LastExecTime = ThisExecTime;

      End if





      Having run my script the following error is being returned, and unsure why, as it is a direct copy from the "incremental load scenarios" document:




      Can anyone spot the mistake, as I can't... (ie "ModificationTime" exists both in my NEW & HISTORY qvd outputs)?


      Any thoughts / suggestions greatly appreciated!


      Kind regards,






      edit:  vs the script found in the "Incremental Load Scenarios" pdf doc, I amended the above script to remove the "SQL select" statements and changing to "Load *" (as not connecting to a database)


      do the "where" statements only belong to the "SQL syntax" and therefore this is my issue?

        • Incremental Load
          Stefan Wühl



          I believe the # hashes in your where clause should be ' single quotes (so I guess it's a typo). Or use numericals like Evan suggested to prevent date format issue.


          I am not sure why you first load new data from your history price file, but maybe I haven't fully understood you setting.


          I believe your requirements should be met using the nice incremental load example in the QV cookbook, available on Rob's download page.