Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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);
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
Thanks Marco, it worked perfectly..
you're welcome
regards
Marco