topic Re: Convert Decimal to Time in QlikView

Convert Decimal to Time

rupshall10 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys

First time resorting to posting on here

I have a time field that arrives in QlikView as a decimal number e.g. 1215

This value represents 12 hours and 15 minutes

I am summing this field and therefore need the value to be represent as 12.25 in decimal or 12:15 in time

Is this possible?

Re: Convert Decimal to Time

vishsaggi — Fri, 06 Oct 2017 15:26:17 GMT

Try this ?

=interval(12.25/24,'h:mm')

Re: Convert Decimal to Time

maxgro — Fri, 06 Oct 2017 15:39:37 GMT

maketime(left(num(field,'0000'),2), right(num(field, '0000'),2))

Re: Convert Decimal to Time

swuehl — Fri, 06 Oct 2017 15:48:04 GMT

Maybe use interval#() to parse your values:

Interval(Interval#(1215,'hmm'))

Re: Convert Decimal to Time

Peter_Cammaert — Fri, 06 Oct 2017 16:00:40 GMT

If you are sure that your time values never exceed 23:59:59, you can also use Time#(D, 'hhmm') to parse your values. Which may be more logical to you.

Otherwise, use Interval#() which will accept any value and recalculate all spillovers into correct values for Days, Hours, Minutes and Seconds.

Re: Convert Decimal to Time

rupshall10 — Wed, 11 Oct 2017 09:23:57 GMT

Thank you for this - it appears to be the best solution however an issues still persists...

The following code

Interval#(ExceptionValue,'hhmm') displays 1215 as 12:15 perfectly and 3645 as 36:45

however it displays 600 as 60:00 instead of 6:00

If I change the code to Interval#(ExceptionValue,'hmm')

600 displays correctly as 6:00 however 1215 displays as 04:35

Is there a way I can have the best of both?

Re: Convert Decimal to Time

swuehl — Wed, 11 Oct 2017 10:43:08 GMT

Maybe

Interval(Interval#(Num(ExceptionValue,'0000'),'hhmm'))

Re: Convert Decimal to Time

rupshall10 — Wed, 11 Oct 2017 10:46:29 GMT

perfect! many thanks