Maintenance Factor Calculations - Vlookup?

[#1]

Hello,

in excel we have maintenance factor calculations of Lift Trucks based on how many hours that truck has been used. Ie) if the truck has been used for 100 hours, the factor is 0.0057 and we would use this factor in our calculation.

The factors are in another sheet in excel, and we would do a vlookup based on the hours. The table looks like this and goes up all the way to 10000 hours:

Hours Factor
1 0.00000
5 0.00029
10 0.00058
15 0.00087
20 0.00116
25 0.00144
30 0.00173
35 0.00202
.. ..
10000 1.0000

The formula IF(Hours<10000,(COST)/(VLOOKUP((Hours),'MAINT. FACTORS'!$B$6:$D$538,3). For trucks less than 10000 hours it will divide the COST by the maintenance factor depending on how many hours it has been used.

I loaded the maintenance factor is an island table in the Qlikview app, and now trying to figure out on how I can use it on our calculation in Qlikview. Does anybody have any ideas?

[#2 · Accepted solution]

Do the trucks always have an hours value that matches one in the Factor Table? For example, could a truck have 7 hours? It looks like you didn't include the final parameter on the VLookup function, so that means an approximate match is returned. I think you need to set up an IntervalMatch (p. 481 of the Reference Guide) to link the Truck Hours to the Factors table.

[#3]

Shinobi-san

I used intervalmatch, and left join. It works perfectly!

Thanks,

Chhai