topic Re: timestamp from oracle into a csv splitting but returns empty in QlikView

timestamp from oracle into a csv splitting but returns empty

Anonymous — Tue, 21 Mar 2017 10:46:16 GMT

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

Re: timestamp from oracle into a csv splitting but returns empty

sunny_talwar — Tue, 21 Mar 2017 11:01:19 GMT

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],

Re: timestamp from oracle into a csv splitting but returns empty

sunny_talwar — Tue, 21 Mar 2017 11:02:02 GMT

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

Re: timestamp from oracle into a csv splitting but returns empty

er_mohit — Tue, 21 Mar 2017 11:09:22 GMT

Have a look to Alt Function.

Convert multiple date formats into one date format using Alt() function - QlikView Blog, QlikView Videos and Tips at Qli…

Re: timestamp from oracle into a csv splitting but returns empty

Anonymous — Tue, 21 Mar 2017 11:13:23 GMT

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