date comparision is not happeing for delta load

[aniruddhyadutta]

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

Thanks much

AD

[Gysbert_Wassenaar]

How is the variable last_Updated_Date created?

[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]

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]

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

[jonathandienst]

Shouldn't that be this?

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

[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

[aniruddhyadutta]

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?