topic Format a number to time in QlikView

Format a number to time

jcharles — Thu, 08 May 2014 06:37:29 GMT

Good day

Please can you help me format a number to a time?

Numbers

600

612

700

730

1805

The times for the above should be:

06:00

06:12

07:00

07:30

18:05

I've tried using Time#(Number,'HH:MM') and Time(Number,'HH:MM') but both do not seem to work.

Please help - Thank you

Re: Format a number to time

CELAMBARASAN — Thu, 08 May 2014 06:40:46 GMT

Hi,

It should be

Time(Time#(Number,'Hmm'), 'HH:mm')

Hope it helps

Re: Format a number to time

tresB — Thu, 08 May 2014 06:41:46 GMT

Try like:

If(Len(Numbers)=3,Time(Time#(Numbers,'hmm') ,'hh:mm'), Time(Time#(Numbers,'hhmm') ,'hh:mm') ) as NewTime

Re: Format a number to time

ashfaq_haseeb — Thu, 08 May 2014 06:42:38 GMT

Hi try below

=time(time#(Numbers,'hmm'),'h:mm')

Regards

ASHFAQ

Re: Format a number to time

jcharles — Thu, 08 May 2014 06:44:03 GMT

Great!!! - Thank you Thank you

Re: Format a number to time

tresB — Thu, 08 May 2014 06:44:55 GMT

Does it work for 4-digit number ?

Re: Format a number to time

Thu, 08 May 2014 06:48:29 GMT

Try this:

LOAD *,

Time#(Mid(Number,1,2) & ':' & Mid(Number,3,2),'HH:mm') as FormatedTime;

LOAD if(len(Number)=3,'0'&Number,Number) as Number;

LOAD * INLINE [

Number

600

612

700

730

1805

];

Re: Format a number to time

jcharles — Thu, 08 May 2014 06:48:59 GMT

Wonderful - this takes into account the change in the length of the number too - Thank you

Re: Format a number to time

CELAMBARASAN — Thu, 08 May 2014 06:57:03 GMT

No I missed that. But you can implement like below, instead of going for If condition

Time(Time#(Num(Number, '0000'), 'HHmm'), 'HH:mm')

Re: Format a number to time

anbu1984 — Thu, 08 May 2014 07:08:12 GMT

Or like this

Time(Time#(Left('0'&Number,4),'HHmm'), 'HH:mm')

Re: Format a number to time

tresB — Thu, 08 May 2014 07:15:38 GMT

Yes, that's a better idea.