topic Re: converting in to time format in QlikView

converting in to time format

manoranjan_d — Mon, 27 Nov 2017 12:24:33 GMT

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

Re: converting in to time format

Anil_Babu_Samineni — Mon, 27 Nov 2017 12:38:56 GMT

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

Re: converting in to time format

Anil_Babu_Samineni — Mon, 27 Nov 2017 12:41:58 GMT

Just this?

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

Re: converting in to time format

manoranjan_d — Mon, 27 Nov 2017 12:42:10 GMT

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

Re: converting in to time format

manoranjan_d — Mon, 27 Nov 2017 12:45:07 GMT

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

Re: converting in to time format

avkeep01 — Mon, 27 Nov 2017 12:47:25 GMT

Hi Manoranjan,

You could use:

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

// 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)

01:55:00

Re: converting in to time format

Anil_Babu_Samineni — Mon, 27 Nov 2017 12:50:10 GMT

Try this?

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

Re: converting in to time format

Chanty4u — Mon, 27 Nov 2017 12:51:57 GMT

may be try with this its working for me

Re: converting in to time format

Or — Mon, 27 Nov 2017 12:58:49 GMT

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.