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

    Incremental Load

    Richard Sheppard

      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:

       

       

      [code]

       

      let ThisExecTime = now();

      NewData:

      Load * from 2a_HISTORY_Pricing.qvd (qvd);

      where ModificationTime >= #$(LastExecTime)#

      and ModificationTime < #$(ThisExecTime)#;

      Concatenate

      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

       

       

      [/code]

       

      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:

       

      Error.gif

       

      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,

       

      Rich

       

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

       

      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

          Rich,

           

          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.

           

          Regads,

          Stefam