Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
;
You're setting the variable wrong
SET vMAXDATE = MAX_DATE
;
should be
LET vMAXDATE = PEEK('MAX_DATE',0,'MAX_DATE')
;
You're setting the variable wrong
SET vMAXDATE = MAX_DATE
;
should be
LET vMAXDATE = PEEK('MAX_DATE',0,'MAX_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?
SET here creates variable with the text value 'MAX_DATE'.
LET attempts to calculate the expression, and assigns the result to the variable.