topic Re: Convert number to time format in QlikView

Convert number to time format

markgraham123 — Thu, 07 Dec 2017 17:21:03 GMT

Hi,

Here is my sample data:

I want to convert number to hh:mm:ss format.

Please see attached app.

Any help is highly appreciated.

311 - 03:11:00

1258 - 12:58:00

84743 - 08:47:43

Re: Convert number to time format

Anil_Babu_Samineni — Thu, 07 Dec 2017 17:56:12 GMT

What are the 311, 1258 ... I mean these are Minutes or Hours or something

Re: Convert number to time format

markgraham123 — Thu, 07 Dec 2017 17:59:02 GMT

Those are numbers.

I have 311, 1258...in my data

I want to show it as the req. O/P

311 - 03:11:00

1258 - 12:58:00

84743 - 08:47:43

Re: Convert number to time format

sunny_talwar — Thu, 07 Dec 2017 18:11:08 GMT

What Time would these be and why?

1254? -> 12:54:00 or 01:25:40

123? -> 12:30:00 or 01:23:00

11231 - > 11:23:10 or 01:12:31

Re: Convert number to time format

markgraham123 — Thu, 07 Dec 2017 20:05:04 GMT

Here is the logic:

If it is 3 digit - 'hmm' 123 is 01:23:00

4 digit - 'hhmm' 1254 is 12:54:00

5 digit - 'hhmms' 11231 is 11:23:01

6 digit - 'hhmmss' 103252 is 10:32:52

Thanks.

Re: Convert number to time format

markgraham123 — Thu, 07 Dec 2017 20:27:01 GMT

I just answered my question . lol

I'm using len(field) to define the format and it worked.

Thanks

Re: Convert number to time format

sunny_talwar — Thu, 07 Dec 2017 20:34:04 GMT

84753 is a five digit number, but it can't be 84:75:30? but you mentioned it to be hhmms? Did you mean hmmss?

Re: Convert number to time format

sunny_talwar — Thu, 07 Dec 2017 20:35:06 GMT

Not sure how you got it, but I used this

Table1:

LOAD Value,

Time(Time#(Num#(Repeat(0, Pick(Match(Len(Value), 3, 4, 5, 6), 1, 0, 1, 0)) & Value & Repeat(0, Pick(Match(Len(Value), 3, 4, 5, 6), 2, 2, 0, 0))), 'hhmmss'), 'hh:mm:ss') as Time;

LOAD * INLINE [

Value

123

1254

11231

103252

311

1258

84743

];

Re: Convert number to time format

markgraham123 — Thu, 07 Dec 2017 21:00:13 GMT

If(len(Value)=6,

Time(Time#(Value,'hhmmss'),'hh:mm:ss'),

If(len(Value)=5,

Time(Time#(Value,'hmmss'),'hh:mm:ss'),

If(len(Value)=4,

Time(Time#(Value,'hmms'),'hh:mm:ss'),

If(len(PLTLM)=3,

Time(Time#(Value,'hmm'),'hh:mm:ss'))))) as Value_Final;

Sorry sunny. 5-digit is 'hmmss'

Re: Convert number to time format

markgraham123 — Thu, 07 Dec 2017 21:00:44 GMT

sorry sunny ..

5 digit is hmmss

Re: Convert number to time format

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:19:30 GMT

It's already been Assume Answered. Not sure, Whether you got to work or not. Perhaps this?

Alt(Date(Date#(Value, 'hmm'),'hh:mm:ss'),Date(Date#(Value, 'hhmm'),'hh:mm:ss'),Date(Date#(Value, 'hhmmss'),'hh:mm:ss'))

LOAD Value, Len, Pick(Match(Len, 3,4,5), Date(Date#(Value,'hmm'), 'hh:mm:ss'),Date(Date#(Value,'hhmm'), 'hh:mm:ss'),Date(Date#(Value,'hhmmss'), 'hh:mm:ss')) as Time;

LOAD *, Len(Value) as Len Inline

[

Value

311

1258

84743

];