Querying a orphan data table depending on the result of the rank() function

[#1]

Hi,

I need your help for a little QlikView project.

I want to calculate a bonus per user, depending on the rank of his working time. For this I have two tables 'User' and 'Bonus' which are orphan and a base bonus value of 50. The bonus of a user is calculated from the total amount of the bonus value divided by the sum of the bonus factors multiplied by the bonus factor of the user.

When writing the corresponding expressions I encountered the following problems:

1. How can I get the value of field BonusFactor depending on the rank of a user if rank value == position value? The function fieldvalue() provides wrong values, because the values in field BonusFactor are not unique. As a workaround, I have increased all values ​​with different decimal places and round them later.

2. The other problem is to sum the previously identified bonus factors.

For better understanding, I have appended a sample file (QV 9.0).

Thanks for your help

Erik

Table User

User	WorkingTime	NoBonus
A	3	0
B	5	1
C	3	0
C	5	0
D	3	0

Table Bonus

Position	BonusFactor
1	2.0
2	1.5
3	1.0
4	0.5
5	0.5
6	0.5

BaseBonusValue (defined): 50

TotalWorkingTime = sum(total WorkingTime)] = 22

TotalBonusValue = sum(total WorkingTime) * BaseBonusValue = 1100

Example for User C:

SumWorkingTime = sum(WorkingTime) = 8

Rank = rank(SumWorkingTime) = 1

UserFactor (Problem 1)  Rank = 1 => BonusFactor at Position 1 = 2.0

TotalSumUserFactor (Problem 2) = sum(total UserFactor)

Bonus = TotalBonusValue / TotalSumUserFactor * UserFactor

Desired Result

Rank	User	WorkingTime	UserFactor	TotalSumUserFactor	Bonus
1	C	8	2.0	4	550
2	A	6	1.5	4	412.5
3	B	5	0	4	0
4	D	3	0.5	4	137.5

[#2]

Sorry, I do not quite understand which variable should I define as inputbox. BonusFactor is a field in a table... a table as variable or what do you mean?