Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Re: Convert time format

Maybe try this

Num(Time)*24

Capture.PNG

13 Replies
MVP
MVP

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;

MVP
MVP

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

MVP
MVP

Re: Convert time format

Maybe try this

Num(Time)*24

Capture.PNG

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