Incremental Load

[#1]

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):

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();

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

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,

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?

[swuehl]

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