converting iso 8601 to timestamp in load script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: converting iso 8601 to timestamp in load script

Peter_Cammaert — Mon, 24 Apr 2017 13:21:21 GMT

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

topic converting iso 8601 to timestamp in load script in QlikView

converting iso 8601 to timestamp in load script

Re: converting iso 8601 to timestamp in load script