topic Re: Cannot sort rows by timestamp in Search the Community

Cannot sort rows by timestamp

SerSwagster — Wed, 30 Jun 2021 15:17:50 GMT

Hello everyone,

on QlikView i tried to sort a table by a column whose datatype is timestamp (with the button "order by numeric value"), but it does not work. I think that QlikView is not recognizing the timestamp format, and so it cannot order the table using this criteria.

The column has this format: 2021-01-22T23:01:32.000Z

On the script sheet I used this setting because I wanted to visualize timestamp in this format: SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

I tried to use timestamp and timestamp# functions, I also tried left(trim) to remove '.000Z' from the string, but no method worked.

This is an example of my attempts:

TimeStamp(Timestamp#(Left(Trim(firstNoDetectionTimestampDT_last), Len(Trim(firstNoDetectionTimestampDT_last))-5)), 'DD/MM/YYYY hh:mm:ss[.fff]'), 'DD/MM/YYYY hh:mm:ss[.fff]') AS NewField

Any suggestions?

Re: Cannot sort rows by timestamp

Vegar — Thu, 01 Jul 2021 05:31:17 GMT

Your enteprator format does not match your source. Try this.

TimeStamp(Timestamp#(Left(Trim(firstNoDetectionTimestampDT_last), Len(Trim(firstNoDetectionTimestampDT_last))-5)), 'YYYY-MM-DD hh:mm:ss')) AS NewField

Re: Cannot sort rows by timestamp

Vegar — Thu, 01 Jul 2021 05:34:48 GMT

Another comment. As your timestamp is formated as YYYY-MM-DD hh:mm:ss it will sort correctly even if it is not recognised as a timestamp. Alphabetical sorting will give you the same sort order as a numerical sorting would do.

Re: Cannot sort rows by timestamp

tresB — Thu, 01 Jul 2021 05:47:54 GMT

Or may be a simpler one like:

Timestamp(Timestamp#(firstNoDetectionTimestampDT_last, 'YYYY-MM-DDWhh:mm:ss.ttttW')) as NewField

I.e. - format the unwanted characters with unwanted but valid formats ('W' here).