Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental Load - Where Clause when generating QVD

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

1 Solution

Accepted Solutions
tanelry
Partner
Partner

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".

View solution in original post

2 Replies
tanelry
Partner
Partner

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".

View solution in original post

Not applicable

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