topic Re: Convert decimal numbers to time in QlikView

Convert decimal numbers to time

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

Hello everybody!

I have 2 tables in excell, A and B.

A represents the number of hours in B in decimals.

The database just have the A column, so I cannot work with the datas of B.

The totals are in the green charts.

My question is, how can I have the '25:20:00' result with '21,6'?

As I said, I only have the 21,6 in qlikview, that has to be transformed into 25:20:00.

Thank you very much!

Re: Convert decimal numbers to time

settu_periasamy — Mon, 05 Sep 2016 19:38:48 GMT

Try this

LOAD Interval(Interval#(Num(Time_Nr,'#.#0'),'h.mm'),'hh:mm:ss') as Time_HHMM,
Num(Time_Nr,'#.#0') as Time_Nr;
LOAD * INLINE [
Time_Nr
1.3
0.1
0.4
3
0.2
0.5
0.5
0.1
1.2
0.2
1.2
3.5
3
0.2
0.2
0.1
0.5
0.2
0.3
1.1
0.1
2.1
0.5
1.1
];

Note: check your decimal separator as well

Re: Convert decimal numbers to time

swuehl — Mon, 05 Sep 2016 19:45:30 GMT

I don't think there is a way to just convert the single value 21,6 to 25:20:00, without knowing the detailed records.

As I understood, you do have access to the detailed records, right? Then first convert every single record to a time value, then aggregate the values, for example like

LOAD *,

Time(Time#(A&'0','h,mm')) as B

INLINE [

"1,3"

"1,1"

"0,2"

];

Then just use

A	Time(Sum(B))
	03:00:00
0,2	00:20:00
1,1	01:10:00
1,3	01:30:00

Re: Convert decimal numbers to time

Anonymous — Tue, 06 Sep 2016 05:13:18 GMT

Thank You Very Much!!

It worked very well