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

[Kushal_Chawda]

see the attached application

[techvarun]

for follow ups

Regards

[#4]

Thanks for your help. But data model and user interface are more complex in reality and the rank depends on other flieds too. Therefore, I can not determine the rank already in the script. Is there another way, without to change the data model?

[Kushal_Chawda]

Can you provide the snapshot of your data?

[Kushal_Chawda]

If there is no link between User & Bonus table then you have to do it from script to get the desire output.

[#7]

You're probably right, but the problem using the script is, that if I change the selection of User, for example only User C and D, then there is a gap in the ranking result.

With the following expression I get the sum of BonusFactor for the current selection:

sum({$ < Position = {"<=$(#=getPossibleCount(User))"} > } BonusFactor)

The only problem is to take users with NoBonus=1 into account. So, is there any way, maybe to get a comma seperated list of the Position (Rank) by getPossibleCount(User) and then using this list, to sum only these values of the field BonusFactor?

I have expanded my document. Maybe someone finds a solution to fix the values of TotalSumUserFactor in the result table.

[Kushal_Chawda]

is it what you are looking for?

[#9]

Actually not really. The BonusFactor per User should dynamicly changed depending from the current User selection and the current User rank, not from the pre defined rank in the data table.

But I think, what I want is not possible and probably not even make sense. So, I will use your solution.

Thank you very much for sharing your knowledge

Erik

[Kushal_Chawda]

Hi,

If you want Bonusfactor dynamic.. then you can define the variable as inputbox.. and use this variable in your expression instead of bonusfactor field.