Cannot sort rows by timestamp

[SerSwagster]

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?

[Vegar]

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

[Vegar]

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.

[tresesco]

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).