Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aniruddhyadutta
Creator
Creator

date comparision is not happeing for delta load

Hi Friends,

I am doing an incremental load from orace db through qliksense script.But the the compariosn in where caluse is not happening which I guess qlik is not able to pass the varibale value to oracle.

If I hard code in the where clause then it works

where LAST_DATE_TIME > TO_DATE('12/20/2016  2:29:57 AM','MM/DD/YYYY HH:MI:SS PM');

But If I am using the variable its not working though the variable is returning value

where LAST_DATE_TIME > TO_DATE('$(last_Updated_Date)','MM/DD/YYYY HH:MI:SS PM');

in qlik I am getting the last_Updated_Date as a format like this

Capture.PNG

Thanks much

AD

12 Replies
Gysbert_Wassenaar

How is the variable last_Updated_Date created?


talk is cheap, supply exceeds demand
sunny_talwar

Are you sure your variable is last_Updated_Date could it be Last_Updated_Date? The reason I say is this because I don't see any other reason for your variable to not work.

aniruddhyadutta
Creator
Creator
Author

its being created like this to check the latest date from a qvd

Last_Updated_Date:

Load

 

    MaxString(LAST_DATE_TIME) as MaxDate

    Resident  MasterData;

   let last_Updated_Date= Peek('MaxDate',0,'Last_Updated_Date');

aniruddhyadutta
Creator
Creator
Author

no Sunny..there is no typo..i double checked again..:(

sunny_talwar

May be this?

Last_Updated_Date:

Load Max(LAST_DATE_TIME) as MaxDate

Resident  MasterData;

LET last_Updated_Date = TimeStamp(Peek('MaxDate',0,'Last_Updated_Date'), 'MM/DD/YYYY h:mm:ss TT');

Gysbert_Wassenaar

Maxstring is for comparing strings ...so '03:40:00 AM' is larger than '02:04:00 PM'. That's why you're getting wrong numbers. You should use the Max function instead. And make sure your date values are really dates and not text strings. Use the Date# or Timestamp# function to turn text values into date/timestamp values if necessary.

Last_Updated_Date:

LOAD TimeStamp(Max(LAST_DATE_TIME),'MM/DD/YYYY hh:mm:ss TT') as MaxDate RESIDENT MasterData;

let last_Updated_Date= peek('MaxDate',0, 'Last_Updated_Date');


talk is cheap, supply exceeds demand
jonathandienst
Partner - Champion III
Partner - Champion III

Shouldn't that be this?

LOAD Max(TimeStamp#(LAST_DATE_TIME,'MM/DD/YYYY hh:mm:ss TT')) as MaxDate

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Gysbert_Wassenaar

Only if his 'date' field contains text values. If it contains dates and you want to use a textual format as in the where clause then formatting what max(...) returns should do the trick


talk is cheap, supply exceeds demand
aniruddhyadutta
Creator
Creator
Author

THANKS a ton gysbert!!! it do worked...:)

Now I have only one query ..it may not be related to qlik bt may to oracle

after I am calculating the max date in my variable  I am using it in the where clause like below:

where LAST_DATE_TIME > TO_DATE('$(last_Updated_Date)','MM/DD/YYYY HH:MI:SS PM');

But I am finding a thing starnge which is that the where caluse do work irrespective of what I put i.e AM or PM in the format of the $(last_Updated_Date)' .By that I mean to say the query is fetching data  even if I write

where LAST_DATE_TIME > TO_DATE('$(last_Updated_Date)','MM/DD/YYYY HH:MI:SS AM');


just for info I can state that $(last_Updated_Date)', is fetching values  as 12/20/2016 03:38:34 AM. at the time when I ran this query.

it seems like the AM/PM format doesn't have any affect.However it is not working if I omit it all together in the format.


any feedback on this?