topic Excel time format in QlikView

Excel time format

Wed, 11 Nov 2009 01:21:35 GMT

Hi everyone,

i'm having a problem with some excel format, that i don't know how to treat in Qlickview.

Excel time format: [ h]:mm:ss. some of the values you can find would be: 3:49:33 || 234:50:22

I just need to get the hour of this values, (3 and 234), but when i do the export to Qlikview something happends whit the load script that transforms excel time format into a decimal number, like 1,783243.

Load script for that value: subfield(TIME,':',1) as TIME, <--- Doesn't work. Transforms value into decimal number.

If i dont do any modification to the time value in the load script, i can display the time just like it appears in Excel (34:55:34), so to get just the hour i have to do write in every place i use the value subfield(TIME,':',1).

Is there anyway to just get the hour in the load script?

Than you.

Jerome.

Excel time format

Wed, 11 Nov 2009 01:31:25 GMT

Use Hour(time) as My_Hour and It should work

Talha

Excel time format

Wed, 11 Nov 2009 01:42:36 GMT

But that would transform the hour value into a 24 hour value. I mean i need first value just like it comes, it can be a value this way 234:45:22 -> that stands for 234 hours, 45 minutes and 22 seconds. Using Hour() function would transform 234 hours into a value between 0 and 24.

Thanks.

Excel time format

Wed, 11 Nov 2009 01:57:25 GMT

Then try using the

subfield(time, ':' ,1)

Talha