topic Re: Timestamp Conversion Issue in QlikView

Timestamp Conversion Issue

Tue, 28 Oct 2014 02:47:37 GMT

Hi all,

Looking for some help with what is most likely a simple issue. I have a timestamp in a csv that I am unable to convert to date and time no matter what I attempt. Format is like this:

TIMESTAMP

26/10/2014 01:06:44:340

Any suggestions on how to break out the date and time portions?

I am looking to use the date in a master calendar and eventually round the time into hours for usage analysis.

Thanks

Re: Timestamp Conversion Issue

JonnyPoole — Tue, 28 Oct 2014 02:54:28 GMT

How about

= date(floor(Timestamp#('26/10/2014 01:06:44:340','DD/MM/YYYY hh:mm:ss:fff')))

= date(floor(Timestamp#( [DateTimeField],'DD/MM/YYYY hh:mm:ss:fff')))

Re: Timestamp Conversion Issue

ashfaq_haseeb — Tue, 28 Oct 2014 05:32:37 GMT

Hi,

Try like this

Load *, date(floor(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Date,

time((Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Time,

hour((Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Hour;

load * Inline

[

TIMESTAMP

26/10/2014 01:06:44:340

]

Regards

ASHFAQ

Re: Timestamp Conversion Issue

jagan — Tue, 28 Oct 2014 06:02:25 GMT

Hi David,

Try like this

Data:

Load *,

Date(Floor(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Date,

Timestamp(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'), 'hh:mm:ss') as Time,

Timestamp(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'), 'hh') as Hour;

load * Inline

[

TIMESTAMP

26/10/2014 01:06:44:340

]

Re: Timestamp Conversion Issue

vardhancse — Tue, 28 Oct 2014 06:08:27 GMT

Date(Floor(TIMESTAMP) as Date,

Time(Floor(TIMESTAMP) as Time

Re: Timestamp Conversion Issue

Tue, 28 Oct 2014 23:15:35 GMT

Thanks Jagan,

All works perfectly with the exception of hour. As I have multiple timstamps, the hours that are returned represent each individual timestamp. So if i have 300 timestamps falling between 1am and 2am, i would like to see only a single entry for 1am. Any thoughts on how to fix this?

David.

Re: Timestamp Conversion Issue

gartigas — Tue, 28 Oct 2014 23:25:04 GMT

Had you tried with:

Left (TimeStampField,1,10) as Date_,

Left (TimeStampField,12,12) as Time_,

or any other Chain treatment that brings you the derired fields...

I've used when the dates come in text format.---

I hope that it helps you...

Regards

Gustavo

Re: Timestamp Conversion Issue

jagan — Wed, 29 Oct 2014 02:43:17 GMT

Hi David,

Try this script

Data:

Load *,

Date(Floor(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Date,

Timestamp(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'), 'hh:mm:ss') as Time,

Hour(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff')) as Hour;

load * Inline

[

TIMESTAMP

26/10/2014 01:06:44:340

26/10/2014 01:25:44:340

];

Regards,

Jagan.

Re: Timestamp Conversion Issue

ashfaq_haseeb — Wed, 29 Oct 2014 03:52:28 GMT

Hi l,

Did you try my solution.

Regards

ASHFAQ

Re: Timestamp Conversion Issue

Wed, 29 Oct 2014 06:20:52 GMT

Works perfectly - thanks Jagan.