converting iso 8601 to timestamp in load script

[Anonymous]

Hi! Thank you for taking the time to read my question and potentially help me!  I am loading an excel sheet into the qlikview document.  One of the columns is a datetime in iso 8601 format (so YYYY-MM-DDTHH:MM:SS+tzn).  I would like to be able to change this into the regular timestamp format; how is this done?

I've tried timestamp(timestamp_field), timestamp#(timestamp_field), and timestamp(timestamp_field, 'YYYY-MM-DD hh:mm:ss').  None of them work.

Thanks!

[Peter_Cammaert]

In theory and when reading Excel binary values, all Date/Time/Timestamp cells will contain both a binary value and a presentation value (your ISO format). QlikView should be able to read these binary vlaues as is, and convert them to whatever format you would like to use in QlikView.

If it still doesn't work, try timestamp#(value, 'YourFormatCodeThatMatchesTheISOInput'). Include all digits that are part of the timestamp but omit anything else, especially the timezone indicator. You'll have to map the timezone separately.

BTW can you post an example of such an Excel timestamp? We now know all cases that don't work, but not what is expected to work...