I put in place this simple incremental load script, it works fine as it's correct, but when i run the load script each time so that i can fetch new/modified records from my data source i end up with wrong data, some become null and the number of rows is not correct.
please tell me what is wrong with the script. thanks in advance.
script is here :
Let exist = isnull(QvdCreateTime('lib://alm_prod_db/test_.qvd'));
If exist=-1 then
[TEST]:
LOAD
[TS_TEST_ID] AS [RN_TEST_ID_TS_TEST_ID],
[TS_NAME],
[TS_STEPS],
[TS_STATUS],
[TS_EXEC_STATUS],
[TS_TYPE],
[TS_CREATION_DATE],
Day([TS_CREATION_DATE]) as [TS_CREATION_DATE_DAY],
Num(Month([TS_CREATION_DATE])) as [TS_CREATION_DATE_MONTH],
Year([TS_CREATION_DATE]) as [TS_CREATION_DATE_YEAR];
SQL SELECT "TS_TEST_ID",
"TS_NAME",
"TS_STEPS",
"TS_STATUS",
"TS_EXEC_STATUS",
"TS_TYPE",
"TS_CREATION_DATE"
FROM "database"."td"."TEST";
Store [TEST] into [lib://alm_prod_db/test_.qvd](qvd);
else
/////
[FetchMaxDate]:
Load Date(Max([TS_CREATION_DATE])) as maxdate FROM [lib://alm_prod_db/test_.qvd](qvd);
LET LastDateInDH = Peek('maxdate');
DROP Table FetchMaxDate;
/////
[TEST_INCREMENTAL]:
LOAD
[TS_TEST_ID] AS [RN_TEST_ID_TS_TEST_ID],
[TS_NAME],
[TS_STEPS],
[TS_STATUS],
[TS_EXEC_STATUS],
[TS_TYPE],
[TS_CREATION_DATE],
Day([TS_CREATION_DATE]) as [TS_CREATION_DATE_DAY],
Num(Month([TS_CREATION_DATE])) as [TS_CREATION_DATE_MONTH],
Year([TS_CREATION_DATE]) as [TS_CREATION_DATE_YEAR];
SQL SELECT "TS_TEST_ID",
"TS_NAME",
"TS_STEPS",
"TS_STATUS",
"TS_EXEC_STATUS",
"TS_TYPE",
"TS_CREATION_DATE"
FROM "database"."td"."TEST"
where "TS_CREATION_DATE" >= '$(LastDateInDH)';
Concatenate LOAD *
FROM [lib://alm_prod_db/test_.qvd](qvd)
WHERE NOT Exists("RN_TEST_ID_TS_TEST_ID");
Store [TEST_INCREMENTAL] into [lib://alm_prod_db/test_.qvd](qvd);
End IF
