Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikviewers,
I hope you can help me out with the issue I am having. I am trying to set up my first incremental load but am encoutering an issue with comparing two timestamps with eachother (goal is to get only new transactions).
This is how I mark the last/newest timestamp from the previous load:
//Load table to enable search for last record
TRA_Max:
LOAD CREATE_TS
FROM TRA_INCOMING.qvd(qvd);
Last_Updated_Date_Table:
load max(timestamp(CREATE_TS)) as Max_CREATE_TS
resident TRA_Max;
Let Last_Transaction = peek('Max_CREATE_TS',0,'Last_Updated_Date_Table');
Drop table TRA_Max;
After this I query the DB using SQL. My load goed into error on the where clause.
WHERE
MAINDB.CREATE_TS >= $(Last_Transaction) AND
MAINDB.TRA_TYPE IN ('AAAA', 'BBBB')
;
This is the error I am getting (in German, sorry):
Auf "MAINDB.CREATE_TS >=" folgte das unerwartete Token "14.06.2018 20". Zu den möglichen Token gehören: "<space>". SQLSTATE=42601
When i change
MAINDB.CREATE_TS >= $(Last_Transaction) AND
to something else, such as:
MAINDB.CREATE_TS BETWEEN '2017-01-01 00:00:00.000000' AND '2017-12-31 00:00:00.000000' AND
Then everything goes well.
What am I doing wrong?
You need to define the proper formatting and changing the variable-call like:
Last_Updated_Date_Table:
load timestamp(max(CREATE_TS), 'YYYY-MM-DD hh:mm:ss.fff') as Max_CREATE_TS
resident TRA_Max;
and
WHERE
MAINDB.CREATE_TS >= '$(Last_Transaction)' AND
MAINDB.TRA_TYPE IN ('AAAA', 'BBBB')
- Marcus
You need to define the proper formatting and changing the variable-call like:
Last_Updated_Date_Table:
load timestamp(max(CREATE_TS), 'YYYY-MM-DD hh:mm:ss.fff') as Max_CREATE_TS
resident TRA_Max;
and
WHERE
MAINDB.CREATE_TS >= '$(Last_Transaction)' AND
MAINDB.TRA_TYPE IN ('AAAA', 'BBBB')
- Marcus
Hi Marcus,
Thanks so much for your help, I just tried it and it works!!
Really great, thanks again!