Issue with comparing two timestamps (incremental load)

[jossebb1]

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?

[marcus_sommer · Accepted solution]

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

[jossebb1]

Hi Marcus,

Thanks so much for your help, I just tried it and it works!!

Really great, thanks again!