topic Num to time in QlikView

Num to time

stabben23 — Mon, 18 Aug 2014 10:38:37 GMT

Hi,

I have a num value =2 this is really 00:00:02 on the clock. I also have 92433 which is 09:24:33

I want the field to be formated as a time value like hh:mm:ss. SQL code or in qlikview is OK.

Re: Num to time

tresB — Mon, 18 Aug 2014 10:46:32 GMT

Try like:

Time(Time#(092433, 'hhmmss')) as Time , and you can then replace this number by a field that holds the similar values.

Re: Num to time

MK_QSL — Mon, 18 Aug 2014 10:48:29 GMT

Load

TimeField,

Time#(Left(Repeat('0',6-Len(TimeField))&TimeField,2) & ':' & Mid(Repeat('0',6-Len(TimeField))&TimeField,3,2)& ':' &Right(Repeat('0',6-Len(TimeField))&TimeField,2),'hh:mm:ss') as NewTimeField

Inline

[

TimeField

434

1123

92433

];

Re: Num to time

ashfaq_haseeb — Mon, 18 Aug 2014 10:50:11 GMT

Hi try below

=Time(Time#(092433, 'hhmmss'),'hh:mm:ss')

Regards

ASHFAQ

Re: Num to time

stabben23 — Mon, 18 Aug 2014 11:19:31 GMT

Hi,

sorry but non of these works, I can tell that my TimeFormat is 'h:mm:ss TT'

Re: Num to time

ashfaq_haseeb — Mon, 18 Aug 2014 11:20:42 GMT

Hi,

Try below

=Time(Time#(Field, 'h:mm:ss TT'),'hh:mm:ss')

Can you attach sample data here.

Regards

ASHFAQ

Re: Num to time

MK_QSL — Mon, 18 Aug 2014 11:22:54 GMT

Provide few lines for that Field along with expected result..

Re: Re: Num to time

stabben23 — Mon, 18 Aug 2014 11:37:24 GMT

Hi, Like this

113, 1256, 211318, 144036, 2

expected 00:01:13, 00:12:56, 21:13:18, 14:40:36, 00:00:02

Re: Re: Num to time

MK_QSL — Mon, 18 Aug 2014 11:39:52 GMT

So you mean to say the formula i have given is not working?

Load

TimeField,

Time#(Left(Repeat('0',6-Len(TimeField))&TimeField,2) & ':' & Mid(Repeat('0',6-Len(TimeField))&TimeField,3,2)& ':' &Right(Repeat('0',6-Len(TimeField))&TimeField,2),'hh:mm:ss') as NewTimeField

Inline

[

TimeField

113

1256

211318

144036

92433

];

Create a table box with TimeField and TimeFieldNew as your two field.... and let me know if you are still not getting the result..

Re: Re: Num to time

stabben23 — Mon, 18 Aug 2014 11:48:03 GMT

Hi,

I'v must have forgott to changed all TimeField to my time field because it really work.

Thanks

Re: Num to time

MarcoWedel — Mon, 18 Aug 2014 12:58:55 GMT

Hi,

another solution:

LOAD *,
    Time(Mod(val,100)/86400+Mod(Div(val,100),100)/1440+Div(val,10000)/24) as Time
INLINE [
    val
    113
    1256
    211318
    144036
    2
];

hope this helps

regards

Marco

Re: Re: Num to time

MarcoWedel — Sun, 24 Aug 2014 22:49:05 GMT

... and one more:

Time(Time#(Num(val, '000000'), 'hhmmss'),'hh:mm:ss')

hope this helps

regards

Marco