Date formatting issue when loading data from CSV file

[Anonymous]

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.

[sunny_talwar]

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

[MarcoWedel · Accepted solution]

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

[Anonymous]

Thanks Marco, it worked perfectly..

[MarcoWedel]

you're welcome

regards

Marco