topic Re: Timestamp convertion from string outputing some null values in App Development

Timestamp convertion from string outputing some null values

angelompcunha — Fri, 30 Sep 2022 17:03:12 GMT

Hello all,

I'm fairly new to Qlik and I've been developing my first app.

I'm getting a set of data from an Oracle Database where one of the fields is a timestamp string in the format YYYY-MM-DD HH:MM:SS.

Then in Qlik when I'm importing the data I'm using SET TimestampFormat='YYYY-MM-DD hh:mm:ss'; at the top to set the default timestamp format and the following statement in the LOAD section to convert the string into a timestamp:

TIMESTAMP(TIMESTAMP#( "Ticket Creation Time IT",'YYYY-MM-DD HH:MM:SS'),'YYYY-MM-DD HH:MM:SS') AS "Ticket Creation DateTime",

My problem is that only some rows are being converted to a timestamp, others are just null.

I haven't found any pattern for this behaviour and have search the web for a possible solution but couldn't find any.

Can anyone lend a help on this topic?

EDIT: Beside the nulls output I also just noticed that the month is also incorrect. In the print above the first row that has a converted value should be 2022-05-03 09:03:51 but instead is being converted to 2022-03-03 09:03:51

Re: Timestamp convertion from string outputing some null values

BrunPierre — Fri, 30 Sep 2022 23:17:24 GMT

Perhaps this instead

Timestamp(Timestamp#("Ticket Creation Time IT",'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') as "Ticket Creation DateTime",

Re: Timestamp convertion from string outputing some null values

angelompcunha — Sat, 01 Oct 2022 18:58:27 GMT

Jeez I feel so dumb., it worked perfectly. I've working with frameworks where I use slightly different format and can be all caps and it didn't occured to me I was simply using the wrong format.

Thanks for the help