Unable to calculate Max date

[aniruddhyadutta]

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

[sunny_talwar]

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

[sunny_talwar]

Also read this....

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

[aniruddhyadutta]

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?

[sunny_talwar]

Max? That seems like a min to me?

[Gysbert_Wassenaar]

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

[aniruddhyadutta]

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.

[sunny_talwar]

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

[Shubham_Deshmukh]

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