Hi,
I have been attepting to get an incremental load working, and I am happy to report after much messing around with the syntax I am almost there... In fact the process works fine right up until I try to drop old data from the old QVD when creating the new QVD.
I believe that the issue is due to an in cosistency on the format of the fields/values being used in the where
The code I am using to create the new QVD is
// Store new QVD
Store Tracker_Template into Tracker_Template.qvd
WHERE (TIMESTAMP_DATE > '$(QVD_START)');
From what I can see in the tables the format of TIMESTAMP_Date is YYYY-MM-DD HH:MM:SS.[FF3] and the variable has been defined as YYYY-MM-DD HH:MM:SS
For some reason I can not work out a way of formating the TIMESTAMP_DATE field from the QVD I have tried using:
timestamp function
Date function
Or doing nothing
And nothing seems to work, has any one got any ideas????
By the way the syntax for generating the variable QVD_START is as follows:
// Picks up the maximum Timestamp data from the data present in the QVD file
Dates:
LOAD
//Max(Timestamp([TIMESTAMP_DATE], 'YYYY-MM-DD HH:mm:ss.FF3')) as TIMESTAMP
Max(Date([TIMESTAMP_DATE], 'YYYY-MM-DD HH:MM:SS')) as MAX_TIMESTAMP,
Min(Date([TIMESTAMP_DATE], 'YYYY-MM-DD 00:00:01')) as MIN_TIMESTAMP,
Date(Now(1), 'YYYY-MM-DD 00:00:01') as QVD_END,
Date(AddMonths(AddYears(MonthEnd((Now(1)-1)),-1),-1), 'YYYY-MM-DD 00:00:01') as QVD_START
From Tracker_Template.qvd (qvd);
// Defines a variable containing the maximum date to be used in the where clause of the incremental extraction
Let MAX_TIMESTAMP = peek ('MAX_TIMESTAMP',0,Dates);
Let MIN_TIMESTAMP = peek ('MIN_TIMESTAMP',0,Dates);
Let QVD_END = peek ('QVD_END',0,Dates);
Let QVD_START = peek ('QVD_START',0,Dates);
