Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental Load Based on Variable Date

I am attempting to create an incremental load where the data from the file (TestData.csv) is loaded into TestDataQVD.qvd only if the "DATE" is greater than the variable date vMAXDATE (based on the max date of the qvd).  Script is below and example case is attached.  I've already performed an initial load, so there is one piece of data in the qvd file.  The .csv file has two rows of data, one of which should load since the DATE (1/24/2014) is greater than vMAXDATE (1/10/2014).  I've tried multiple ways of converting the the "DATE" field to match the variable format without success.

//Finding the max date value in the QVD

MAX_DATE:

LOAD

     MAX(DATE) AS [MAX_DATE]

FROM TestDataQVD.qvd (qvd)

;

//Setting the variable value to the "MAX_DATE" field determined in previous step

SET vMAXDATE = MAX_DATE

;

//Loading data currently in the QVD

TEST_DATA:

LOAD DISTINCT

      *

FROM TestDataQVD.qvd (qvd)

;

//Loading the new data from the CSV file, concatenating it to the QVD date

LOAD DISTINCT

     ID,

     DATE,

     DEDCD,

     DED_AMT

FROM TestData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

//Only load where the DATE is greater than the variable vMAXDATE

WHERE DATE > '$(vMAXDATE)'

;

//Storing all of the data (QVD + CSV) in the QVD

STORE TEST_DATA


INTO TestDataQVD.qvd (qvd)

;

//Reloading the data from the QVD for verification

LOAD

     *

FROM TestDataQVD.qvd (qvd)

;

1 Solution

Accepted Solutions
Nicole-Smith

You're setting the variable wrong

SET vMAXDATE = MAX_DATE

;

should be

LET vMAXDATE = PEEK('MAX_DATE',0,'MAX_DATE')

;

View solution in original post

3 Replies
Nicole-Smith

You're setting the variable wrong

SET vMAXDATE = MAX_DATE

;

should be

LET vMAXDATE = PEEK('MAX_DATE',0,'MAX_DATE')

;

Not applicable
Author

Works perfectly.  Thank you.  For my knowledge bank, why does it need to be set in this manner?  What is the difference between the two ways?

Anonymous
Not applicable
Author

SET here creates variable with the text value 'MAX_DATE'.

LET attempts to calculate the expression, and assigns the result to the variable.