topic problem with timestamp to get max value in QlikView

problem with timestamp to get max value

Anonymous — Mon, 11 Jan 2016 13:08:57 GMT

Hi,

I have timestamp column and trying to get max value from this function. But it is displaying null value

=MaxString(Date(Floor(Timestamp#(account_created_datetime)),'DD/MM/YYYY')).

Plese help me on this

Regards,

Kumar

Re: problem with timestamp to get max value

sunny_talwar — Mon, 11 Jan 2016 13:11:50 GMT

Firstly you havn't specified what format your timestamp for QlikView to understand:

Try something like this:

=Max(Date(Floor(Timestamp#(account_created_datetime, 'TimeStampFieldFormatHere')),'DD/MM/YYYY'))

Re: problem with timestamp to get max value

swuehl — Mon, 11 Jan 2016 13:12:01 GMT

Your Timestamp#() function seems not properly parsed the account_created_datetime values.

Please post some sample data.

Re: problem with timestamp to get max value

Anonymous — Mon, 11 Jan 2016 13:48:55 GMT

Hi Sunny,

Thanks for your reply.

I have made changes, but no use.

Max(Date(Floor(Timestamp#(account_created_datetime,'DD/MM/YYYY hh:mm:ss')),'DD/MM/YYYY'))

actually dates are populated like below

Re: problem with timestamp to get max value

sunny_talwar — Mon, 11 Jan 2016 13:52:15 GMT

you mean that you are still seeing the time component? Would you be able to share a sample of where the above provided expression isn't working?

Re: problem with timestamp to get max value

swuehl — Mon, 11 Jan 2016 13:53:30 GMT

Try

Max(Date(Floor(Timestamp#(account_created_datetime,'DD/MM/YYYY hh:mm:ss.fff')),'DD/MM/YYYY'))

Re: problem with timestamp to get max value

sunny_talwar — Mon, 11 Jan 2016 13:55:48 GMT

Good catch Stefan. This should also work:

Max(Date(Floor(Timestamp#(SubField(account_created_datetime, '.', 1),'DD/MM/YYYY hh:mm:ss')),'DD/MM/YYYY'))

Re: problem with timestamp to get max value

jonathandienst — Mon, 11 Jan 2016 14:00:59 GMT

Please avoid the Max(Date(Floor ....) redundant formatting operation between the Floor (returns a number) and Max (returns a number). The Date in between serves no purpose as the format is immediately overridden by the Max.

I think you probably mean:

Date(Max(Floor(Timestamp#(account_created_datetime, 'DD/MM/YYYY hh:mm:ss.fff'))), 'DD/MM/YYYY')

Re: problem with timestamp to get max value

Anonymous — Mon, 11 Jan 2016 14:06:13 GMT

Thanks sunny and it is working.

I have a doubt why did you mentioned subfield of '.' ,1 Can you explain

Re: problem with timestamp to get max value

Anonymous — Mon, 11 Jan 2016 14:10:59 GMT

Thanks Jonathan, I am trying to get max month from this date.

Re: problem with timestamp to get max value

Mon, 11 Jan 2016 14:17:17 GMT

Max(Date(Floor(Timestamp#(account_created_datetime, 'TimeStampFieldFormatHere')),'DD/MM/YYYY'))

Re: problem with timestamp to get max value

sunny_talwar — Mon, 11 Jan 2016 14:40:44 GMT

Because your date is 11/06/2015 09:40:56.000000

those extra 0s at the end either needs to be removed using SubField() function (SubField(FieldName, '.', 1) means take everything before the dot)

or you can use what Stefan prescribed:

Max(Date(Floor(Timestamp#(account_created_datetime,'DD/MM/YYYY hh:mm:ss.fff')),'DD/MM/YYYY'))

here the extra 0s are taken care by .fff