Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jossebb1
Contributor III
Contributor III

Issue with comparing two timestamps (incremental load)

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?

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

jossebb1
Contributor III
Contributor III
Author

Hi Marcus,

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

Really great, thanks again!