Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ioannagr
Creator III
Creator III

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
TimvB
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!

Rodj
Luminary Alumni
Luminary Alumni

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