I try to make an incremental load work by first loading the current data into FAC_OND_DELEN_X from qvd and then determine the latest record and store that into a variable Start_Key.
The I load the new records from the DB2 database except the variable Start_Key in the Where Clause doesn't work.
Who can help me?
FAC_OND_DELEN_X:
LOAD*
FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);
LET Start_Key_Inc = Max(OND_KEY);
Concatenate
FAC_OND_DELEN_NEW:
LOAD *;
SQL SELECT *
FROM DWH.FAC_OND_DELEN
Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_KEY > $(Start_Key_Inc); // Also Tried '$(Start_Key_Inc)'
I have put The Max function in a load statement and combined that with a peek function. After matching the notation of the date and adding the '' in the where clause it works like clockwork. Thanx!
FAC_OND_DELEN_X:
LOAD*
FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);
MAXKEY:
LOAD
Date(Max(OND_PER)-1, 'DD/MM/YYYY') AS MAXDATE
Resident FAC_OND_DELEN_X;
LET Start_Key_Inc = Date(Peek('MAXDATE',0), 'DD/MM/YYYY');
DROP Table MAXKEY;
Concatenate
FAC_OND_DELEN_NEW:
LOAD *,
OND_YEAR & '_' & OND_OGM AS OND_YEAR_OG;
LOAD *,
Year(OND_PER) AS OND_YEAR,
If(Year(OND_PER) = 2015 AND OND_OG = 39, 13, OND_OG) AS OND_OGM;
SQL SELECT *
FROM DWH.FAC_OND_DELEN
Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_PER > '$(Start_Key_Inc)';
