topic Converting time to number : hhhh:mm:ss towards number,decimal in QlikView

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

Wed, 03 Mar 2010 14:21:42 GMT

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.

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

Wed, 03 Mar 2010 15:18:06 GMT

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

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

johnw — Wed, 03 Mar 2010 22:55:53 GMT

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"