Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Incremental Load Based on Variable Date

You're setting the variable wrong

SET vMAXDATE = MAX_DATE

;

should be

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

;

3 Replies

Re: Incremental Load Based on Variable Date

You're setting the variable wrong

SET vMAXDATE = MAX_DATE

;

should be

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

;

Not applicable

Re: Incremental Load Based on Variable Date

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?

mov
Esteemed Contributor III

Re: Incremental Load Based on Variable Date

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

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

Community Browser