10 Replies Latest reply: Jul 25, 2013 3:23 AM by Erik Jung

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

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).

Erik

Table User

UserWorkingTimeNoBonus
A30
B51
C30
C50
D30

Table Bonus

PositionBonusFactor
12.0
21.5
31.0
40.5
50.5
60.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

RankUserWorkingTimeUserFactorTotalSumUserFactorBonus
1C82.04550
2A61.54412.5
3B5040
4D30.54137.5
• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

see the attached application

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

Regards

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

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?

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

Can you provide the snapshot of your data?

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

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

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

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.

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

is it what you are looking for?

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

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

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

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.

• ###### Re: How to sum total values get by fieldvalue() function in straight table or alternative function

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?