Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
manoranjan_d
Contributor III

converting in to time format

I am pulling some data from Excel, a column has value in min and second (e.g. 115.533333333333 ). this is 115 minutes and rest are milliseconds


I want to convert this value in HH:MM:SS


Interval(round(elapsed_time_in_minutes*60),'ss') as elapsed_time_in_min_1,

            Interval(round(elapsed_time_in_minutes*60),'mm.ss') as elapsed_time_in_min_2,

            Interval(round(elapsed_time_in_minutes),'mm.ss') as elapsed_time_in_min_3,

            interval(timestamp#(elapsed_time_in_minutes,'s'),'hh:mm:ss') as elapsed_time_in_min_4,

            Interval(Interval#(elapsed_time_in_minutes/1000,'ss'),'mm:ss')as elapsed_time_in_min_6,

            TimeStamp((elapsed_time_in_minutes/(1000*60*60*24)),'mm:ss')as elapsed_time_in_min_5;


but non are working


pls help me

8 Replies
Highlighted

Re: converting in to time format

Do you know the value of given number in hh:mm:ss format??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

Re: converting in to time format

Just this?

=Interval(Interval#('115.533333333333','mm.fff'), 'HH:mm:ss')

Capture.JPG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
manoranjan_d
Contributor III

Re: converting in to time format

hi anil,

basic requirement is 115.53333

115 minutes and rest are milliseconds


now in the script level i want to convert this value in HH:MM:SS

Highlighted
manoranjan_d
Contributor III

Re: converting in to time format

hi anil,

pls dont pass value its a field i want to covert this into the script level not int he chart or test box level

Highlighted
Partner
Partner

Re: converting in to time format

Hi Manoranjan,

You could use:

INTERVAL(elapsed_time_in_minutes/60/24,'hh:mm:ss')

Or

// only inminutes

INTERVAL(FLOOR(elapsed_time_in_minutes)/60/24,'hh:mm:ss')

If the decimal numbers are in miliseconds (not seconds) you could use:

=INTERVAL((FLOOR(elapsed_time_in_minutes)/60/24)+(elapsed_time_in_minutes - FLOOR(elapsed_time_in_minutes))/100/60/24,'hh:mm:ss')

Results:

01:55:32 (1 hour, 55 minutes and 32 seconds)

Or

01:55:00

Highlighted

Re: converting in to time format

Try this?

=Interval(Interval#(FieldName,'mm.fff'), 'HH:mm:ss')


Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Chanty4u
Esteemed Contributor III

Re: converting in to time format

may be try with this  its working for me

tim.PNG

Highlighted
Or
Valued Contributor II

Re: converting in to time format

I think you're presenting the value wrong - there's no way the value after the period is milliseconds (533333333333). If it was, it would represent roughly 17 years.

If it represents part of a minute, you have to figure out if it represents a fraction (in which case the value is roughly 01:55:31) or a representation of seconds (in which case the value is 01:55:53). Neither one is particularly complicated to format, but you have to know which one you're looking for.