Incremental Load Based on Variable Date

Fri, 07 Feb 2014 16:03:13 GMT

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)

;

Re: Incremental Load Based on Variable Date

Nicole-Smith — Fri, 07 Feb 2014 16:13:50 GMT

You're setting the variable wrong

SET vMAXDATE = MAX_DATE

;

should be

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

;

Re: Incremental Load Based on Variable Date

Fri, 07 Feb 2014 16:21:18 GMT

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?

Re: Incremental Load Based on Variable Date

Anonymous — Fri, 07 Feb 2014 16:23:52 GMT

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

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

topic Re: Incremental Load Based on Variable Date in QlikView

Incremental Load Based on Variable Date

Re: Incremental Load Based on Variable Date

Re: Incremental Load Based on Variable Date

Re: Incremental Load Based on Variable Date