Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sophielavielle1
New Contributor II

Convert time format

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

1 Solution

Accepted Solutions
Highlighted

Re: Convert time format

Maybe try this

Num(Time)*24

Capture.PNG

13 Replies

Re: Convert time format

May be this

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

sophielavielle1
New Contributor II

Re: Convert time format

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

dwforest
Valued Contributor

Re: Convert time format

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

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;

Life is so rich, and we need to respect to the life !!!

Re: Convert time format

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

sophielavielle1
New Contributor II

Re: Convert time format

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:530,05
0:16:480,28
0:10:000,17
0:14:510,25
0:10:000,17
0:10:000,17
1:19:171,32
0:00:000,00
0:00:000,00

Thank you

sophielavielle1
New Contributor II

Re: Convert time format

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

Highlighted

Re: Convert time format

Maybe try this

Num(Time)*24

Capture.PNG

MVP & Luminary
MVP & Luminary

Re: Convert time format

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