topic Re: Date formatting issue when loading data from CSV file in QlikView

Date formatting issue when loading data from CSV file

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

Hello Community,

I have uploaded below excel (csv) file into Qlik. I am trying to format Column [Received Date] as time stamp but it doesn't work as expected. If you see, it is working fine when the hour is a double digit, but failing when to convert it into time stamp when the hour is in single digit.

Can somebody help with it? Has anyone seen this before?

LOAD ID,

[Received Date],

Timestamp( Timestamp#( [Received Date],'MM/DD/YY hh:mm:ss TT'),'MM/DD/YY hh:mm:ss TT') as [New Received Date]

FROM

[..\..\.\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Output File

ID	Received Date	New Received Date
1	02/29/16 12:54:47 PM	02/29/16 12:54:47 PM
2	02/29/16 12:55:31 PM	2/29/2016 12:55:31 PM
3	01/05/16 1:59:05 PM
4	01/05/16 2:46:13 PM
5	01/05/16 2:55:32 PM
6	01/05/16 3:06:20 PM
7	02/01/16 11:26:52 AM	02/01/16 11:26:52 AM
8	02/04/16 11:37:40 AM	02/04/16 11:37:40 AM

Any help is appreciated.

Re: Date formatting issue when loading data from CSV file

sunny_talwar — Thu, 10 Mar 2016 21:55:59 GMT

Try this:

Removed one h from hh:mm:ss TT

LOAD ID,

[Received Date],

Timestamp( Timestamp#( [Received Date],'MM/DD/YY h:mm:ss TT'),'MM/DD/YY hh:mm:ss TT') as [New Received Date]

FROM

[..\..\.\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Re: Date formatting issue when loading data from CSV file

MarcoWedel — Thu, 10 Mar 2016 22:22:27 GMT

Hi,

you seem to have double spaces in your timestamps between date and time in those cases. So one solution might be:

LOAD ID,
    [Received Date],
    Timestamp#(Replace([Received Date],'  ',' '),'MM/DD/YY hh:mm:ss TT') as [New Received Date]
FROM QlikCommunity_Thread_209071.csv (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

hope this helps

regards

Marco

Re: Date formatting issue when loading data from CSV file

Anonymous — Thu, 10 Mar 2016 22:31:25 GMT

Thanks Marco, it worked perfectly..

Re: Date formatting issue when loading data from CSV file

MarcoWedel — Thu, 10 Mar 2016 22:44:24 GMT

you're welcome

regards

Marco