Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

variable in where sql statement (incremental load)

Hi all.

I have a field called  updated   which is a  timestamp field which in Postgres has the following format : 'YYYY-MM-DD hh:mm:ss.fff'

I loaded the table in which it belongs in data load script and formatted it as such timestamp(date(updated),'YYYY-MM-DD h:mm:ss.fff ') as updated 

 

I also have the statement  Let Last_Update_Date=timestamp(date(Peek('MaxDate',0,'Last_Update_Date')),'YYYY-MM-DD h:mm:ss.fff ');       because i'm trying to do an incrememental load.However, in the where condition in the sql part of the load which is  where updated> $(Last_Update_Date);

, i get errors all the time and i'm pretty sure it's the format.

What is happening?!

 

Your help will be a lot appreciated!

 

2 Replies
Highlighted
Creator II
Creator II

I would first try the where clause without the variable, so just insert the a timestamp in text.

If this works, run the script in debug modus until the variable is created and check if the format is correct. 

If it still does not work, try the following variable expression:

Let Last_Update_Date = text(timestamp(date(Peek('MaxDate',0,'Last_Update_Date')),'YYYY-MM-DD h:mm:ss.fff '));

In case this still does not work, try using double straight quotes around the where clause field:

WHERE "updated" > $(Variable);

Also, check if the field “updated” is written correct.

I hope one if the options works!

Highlighted
Luminary
Luminary

I haven't tried it with postgres but typically you need to put a text() function around your formatting as Tim as suggested.

In an app I have curently I load a timestamp from a csv file then:

vL.CurrentExtractTimestamp = Text(Timestamp(Peek('CurrentEDWTimestamp', 0, 'Current_Timestamp_Table'), 'YYYY-MM-DD hh:mm:ss'));

Then I query the source table with a:

where MyTimeStampField > vL.CurrentExtractTimestamp