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();
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;
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!
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?
QV Load Process.xlsx 13.4 K