Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
guiecokey22
Contributor III
Contributor III

Convert decimal numbers to time

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!

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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
]
;

Capture.JPG

Note:  check your decimal separator as well

View solution in original post

3 Replies
settu_periasamy
Master III
Master III

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
]
;

Capture.JPG

Note:  check your decimal separator as well

View solution in original post

swuehl
MVP
MVP

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 [

A

"1,3"

"1,1"

"0,2"

];

Then just use

A Time(Sum(B))
03:00:00
0,200:20:00
1,101:10:00
1,301:30:00
guiecokey22
Contributor III
Contributor III
Author

Thank You Very Much!!

It worked very well