Incremental Load

Mon, 21 May 2012 13:26:06 GMT

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?

Incremental Load

swuehl — Mon, 21 May 2012 18:05:39 GMT

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

topic Incremental Load in QlikView

Incremental Load

Incremental Load