Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
How is the variable last_Updated_Date created?
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.
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');
no Sunny..there is no typo..i double checked again..:(
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');
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');
Shouldn't that be this?
LOAD Max(TimeStamp#(LAST_DATE_TIME,'MM/DD/YYYY hh:mm:ss TT')) as MaxDate
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
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?