Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

timestamp from oracle into a csv splitting but returns empty

Hi all

i have the following dates from a oracle sql table stored in a CSV file. when this is to be split to Transaction Date and Time separately, the below data has problems(empty fields) when your hour clock is single digit. i am thinking this is due to the extra space between 2017 and hour or date formatting issue

20/03/2017  1:23:00 PM,   --> doesnt work

20/03/2017  3:56:00 PM,  --> doesnt work

20/03/2017  3:19:00 PM,  --> doesnt work

20/03/2017  3:18:00 PM,  --> doesnt work

19/03/2017  6:07:00 PM,  --> doesnt work

20/03/2017  9:09:00 AM,  --> doesnt work

20/03/2017 10:09:00 AM,  --> split correctly

20/03/2017 11:54:00 AM,  --> split correctly

date(floor([TRANSACTION DATETIME])) as [Transaction Date],

  time(frac([TRANSACTION DATETIME])) as [Transaction Time],

Can you tell me the ways to fix this pls

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this may be

Date(Floor(TimeStamp#(PurgeChar([TRANSACTION DATETIME], ' '), 'DD/MM/YYYYh:mm:ssTT'))) as [Transaction Date],

Time(Frac(TimeStamp#(PurgeChar([TRANSACTION DATETIME], ' '), 'DD/MM/YYYYh:mm:ssTT'))) as [Transaction Time],

View solution in original post

4 Replies
sunny_talwar

Try this may be

Date(Floor(TimeStamp#(PurgeChar([TRANSACTION DATETIME], ' '), 'DD/MM/YYYYh:mm:ssTT'))) as [Transaction Date],

Time(Frac(TimeStamp#(PurgeChar([TRANSACTION DATETIME], ' '), 'DD/MM/YYYYh:mm:ssTT'))) as [Transaction Time],

sunny_talwar

I think the issue seems to be that there are two spaces between date and time when hour is single digit and purgeChar() with TimeStamp#() might help here

Anonymous
Not applicable
Author

Thanks Sunny that was quick and works like a charm. yes it is the space that troubled a bit