Converting time to number : hhhh:mm:ss towards number,decimal

[#1]

In my database I keep person codes, project codes and hours_spent, as well as travel time and a fee/hour.

The format in which the hours_spent spent is stored is hhhh:mm:ss. In order to calculate the cost, you need to sum the hours spent (on a certain project for example) and multiply with the cost / hour.

However, the database field shows for example :

person project code hours spent travel time cost

A 123 25:30:00 01:00:00 45

B 123 5:00:00 45

Total hours spent = 31:30:00. However, you cannot just multiply this field with the cost.

Is there an easy way to convert 31:30:00 towards 31,5 hours

I have also encountered problems when I read the hours_spent field as a time(time#(hours_spent, 'hhhh:mm:ss')) field.

Qlikview stops at 24:00:00. Do I need to convert the database field into another format. So briefly :

=> what is the best way to store an hours spent field that is in database format hhhh:mm:ss in Qlikview

=> how can I quickly convert this into a format which can be used for calculations (multiplying with a cost/hour) for example

Running on QV9 SR2

Thanks.

[#2]

In the Hours:Mins value, you can use subfield and seperate the Hours and Mins first, then u can convery the minutes into its equivalent hour (by dividing it by 60) then adding with Hours.now we will be having the total time in Hours only; so now we can multiply the total Hours time with cost(ensure currency exchange rate is also considered).

this could be the simplest solution for this problem.

Regards,

Santhosh

[johnw]

This should work as well:

interval#(hours_spent,'hhhh:mm:ss')*24

Edit: Answering your questions a little more explicitly...

As you've noticed, time#() can only process times that are less than a day. Basically, it is intended to be a time on a clock, not an elapsed time. To store an elapsed time, use iterval or interval#.

I would suggest storing the data in QlikView as an interval, probably loaded something like this:

interval(interval#(YourDatabaseField,'hhhh:mm:ss'),'hhhh:mm:ss') as "Time Spent"

The underlying numeric value for an interval is days, so you just multiply by 24 to get hours:

"Time Spent" * 24 * "Cost Per Hour"