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

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

       

      Thanks for your help

      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