Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aniruddhyadutta
New Contributor

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:MISmiley FrustratedS 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:MISmiley FrustratedS PM');

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

Capture.PNG

Thanks much

AD

12 Replies

Re: date comparision is not happeing for delta load

How is the variable last_Updated_Date created?


talk is cheap, supply exceeds demand
MVP
MVP

Re: date comparision is not happeing for delta load

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.

Highlighted
aniruddhyadutta
New Contributor

Re: date comparision is not happeing for delta load

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
New Contributor

Re: date comparision is not happeing for delta load

no Sunny..there is no typo..i double checked again..Smiley Sad

MVP
MVP

Re: date comparision is not happeing for delta load

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');

Re: date comparision is not happeing for delta load

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
MVP
MVP

Re: date comparision is not happeing for delta load

Shouldn't that be this?

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

Re: date comparision is not happeing for delta load

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
New Contributor

Re: date comparision is not happeing for delta load

THANKS a ton gysbert!!! it do worked...Smiley Happy

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:MISmiley FrustratedS 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:MISmiley FrustratedS 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?

Community Browser