Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
it seems a very simple issue but unable to resolve
I have a 'DATETIME' field in my table whose format is 'MMDDYYYYHHMMSS'
I just want to calculate the max date .
Last_Updated_Date:
Load
Max (DATETIME)as MaxDate
Resident MasterData;
let last_Updated_Date= Peek('MaxDate',0,'Last_Updated_Date');
But MaxDate is returning as below
its treating DATETIME as string i suppose..how to convert it to date?
cheers
AD
May be help QlikView understand your DATETIME field
MasterData
LOAD TimeStamp#(DATETIME, 'MMDDYYYYhhmmss') as DATETIME,
....
FROM ....
Last_Updated_Date:
Load
Max (DATETIME)as MaxDate
Resident MasterData;
let last_Updated_Date= Peek('MaxDate',0,'Last_Updated_Date');
Hi Sunny,
Thanks for your reply.I will obviously go through the post as you have mentioned.
but as of now I am getting the below values you have suggested like below
the DATETIME filed as you see I have sorted numerically and the expected Max date should be 12092016002920.
here I am assuming the DATETIME field is in timestamp format in database but I now suspect it might be string.so do i need to convert it to timestamp?
Max? That seems like a min to me?
12092016002920
That still looks like a text string (since it's left aligned in the filter pane). Perhaps you need to use the timestamp# function to turn it into a date value:
Timestamp(Timestamp#(DATETIME,'MMDDYYYYhhmmss'),'MM/DD/YYYY hh:mm:ss') as DATETIME
Hi Sunny and gysbert ,
I am sorry I given the min screenshot earlier.
Thats what I originally suspected.
Now I have converted the datetime field as time stamp and they are right aligned.
need to do the max correctly now.
This should work not (using new DATETIME)
Last_Updated_Date:
Load
Max (DATETIME)as MaxDate
Resident MasterData;
let last_Updated_Date= Peek('MaxDate',0,'Last_Updated_Date');
Hi Gysbert,
This is not working for me, dont know why...
I am taking max of Timestamp(Timestamp#(DATETIME,'MMDDYYYYhhmmss'),'MM/DD/YYYY hh:mm:ss') as DATETIME
Given format - MMM DD YYYY hh:mm:ss:fff TT
Required format - MM-DD-YYYY hh:mm:ss
Rgds,
Shubham