topic Re: Convert time format in QlikView

Convert time format

Anonymous — Thu, 25 Jan 2018 08:08:39 GMT

Hello

in my excel file, I have data in the format hh: mm: ss that I want to transform in the format hh, m in the script when loading data.

today I can not transform my data into the desired format.

Thanks for your help

Re: Convert time format

sunny_talwar — Thu, 25 Jan 2018 17:24:38 GMT

May be this

Time(Time#(FieldName, 'hh:mm:ss'), 'hh, m') as FieldName

Re: Convert time format

Anonymous — Fri, 26 Jan 2018 13:40:55 GMT

Thank you but it does work, I have nothing in my new field...

Re: Convert time format

dwforest — Mon, 29 Jan 2018 23:56:06 GMT

You may need to manually parse the data if it is formatted as General (string) rather than a date/time in Excel

Time(MakeTime(num(left(FieldName,2)), num(mid(FieldName,3,3))),'hh, m')

Re: Convert time format

Anil_Babu_Samineni — Tue, 30 Jan 2018 00:26:09 GMT

today I can not transform my data into the desired format.

Not sure, I followed this part.

Perhaps this?

If FieldName = Today() Then

Load FieldName From Table;

Else

Load Time(Time#(FieldName, 'hh:mm:ss'), 'hh, m') as FieldName From Table;

End If;

Re: Convert time format

sunny_talwar — Tue, 30 Jan 2018 12:32:57 GMT

Would you be able to share few rows of data to see your issue?

Re: Convert time format

Anonymous — Tue, 30 Jan 2018 12:38:05 GMT

Here are some lines of my Excel file.

I have also some hours in the colum "Time" which are higher than 24:00:00 (max: 239:00:00)

I created the second column to be able to calculate the total.

Time Time in hour

0:02:53	0,05
0:16:48	0,28
0:10:00	0,17
0:14:51	0,25
0:10:00	0,17
0:10:00	0,17
1:19:17	1,32
0:00:00	0,00
0:00:00	0,00

Thank you

Re: Convert time format

Anonymous — Tue, 30 Jan 2018 12:38:38 GMT

No I doesn't work, I have "-" in my field

Re: Convert time format

sunny_talwar — Tue, 30 Jan 2018 17:46:02 GMT

Maybe try this

Num(Time)*24

Re: Convert time format

rwunderlich — Tue, 30 Jan 2018 17:59:29 GMT

If you parse this using the Interval#() function it should be correct, even when hh>24.

Interval(Interval#(Time, 'h:mm:ss'))

Can you post the script you are trying?

-Rob

Re: Convert time format

dwforest — Tue, 30 Jan 2018 18:25:56 GMT

Typo should be 4,2 for mid, not 3,3. This also "assumes" the hour part of your string is always 2 digits.

Time(MakeTime(num(left(FieldName,2)), num(mid(FieldName,4,2))),'hh, m')

Rob's answer of interval does not have this restriction so I would go with that.

Re: Convert time format

sunny_talwar — Tue, 30 Jan 2018 18:30:50 GMT

Or may be this

Table:

LOAD RowNo() as RowNum,

Time,

Num(Interval#(Time, 'h:mm:ss'))*24 as [Time in hour];

LOAD * INLINE [

Time

0:02:53

0:16:48

0:10:00

0:14:51

0:10:00

1:19:17

0:00:00

239:00:00

];

Re: Convert time format

Anonymous — Wed, 31 Jan 2018 10:44:27 GMT

Thank you it works perfectly!

It was easy in fact..

Re: Convert time format

Anonymous — Wed, 31 Jan 2018 10:45:16 GMT

Thank you it works perfectly!

It was easy in fact..