Incremental Load - Where Clause when generating QVD

Fri, 06 Aug 2010 13:25:21 GMT

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

Incremental Load - Where Clause when generating QVD

tanelry — Fri, 06 Aug 2010 14:04:00 GMT

The Store statement does not take where-condition. You can only store full set of data from a table or fields. And storing will always replace the old QVD file (no update or append records).
There are good examples in QV help file, under index "QVD files and Incremental Load".

Re: Incremental Load - Where Clause when generating QVD

Fri, 12 Oct 2012 22:48:28 GMT

you can use temporal tables in your database (like ##name) and exctract only the information that you need.

topic Re: Incremental Load - Where Clause when generating QVD in QlikView

Incremental Load - Where Clause when generating QVD

Incremental Load - Where Clause when generating QVD

Re: Incremental Load - Where Clause when generating QVD