3 Replies Latest reply: Jul 14, 2014 12:50 PM by Erica Whalley RSS

    How to evenly distribute random values to agents

    Darrin Pilkington

      I have for example 20 agents and 10,000+ accounts I want them to be working on.  The accounts very in the type.  I am trying to randomly assign and even amount to each person. 

      Also, the number of agents can change per day.

       

      I feel like Tresesco's idea might get me close.  Pick() random order

      Possibly replace RecNo() As DoorNo with the Account# and DoorPrize with the Agents but how to get an even spread of accounts per agent.

       

      Tab1:
      Load

           RecNo() As DoorNo,
           Pick(RecNo(),'Car','Goat','Goat') as DoorPrize
           AutoGenerate 3;

      Tab2:
      Load
             Concat(DoorNo, ';', Rand()) as DoorNo2               // This one is the trick
      Resident Tab1;

      Let vDoors=Peek('DoorNo2');


      Tab3:
      Load
      SubField('$(vDoors)', ';', RecNo()) & '|' & DoorPrize as Door
      Resident  Tab1;

      Drop Tables Tab1, Tab2;

       

      Thanks for your help.

        • Re: How to evenly distribute random values to agents

          Hi Darrin

           

          I've not looked at tresecco's idea but this is how I would do it

           

          - Start by adding random numbers to the accounts

          Accounts: Load rowno() as AccNo, rand() as RandNo AutoGenerate(12345);

           

          - Add a "Random" integer from 1 to the total number of rows by sorting by the random number, and adding the rownumber:

          Accounts_Sorted:

          Load AccNo,  Rowno() as Rank resident Accounts order by RandNo;

           

          - Work out how many Accounts each agent needs, put this in a variable:

          let vNumAgents = 20;

          let vNumAccounts = NoOfRows('Accounts');

          let vFactor= $(vNumAccounts)/$(vNumAgents);

           

          - Divide the random "Rank" integer in the accounts table by this figure and round up to get the number for the Agent.

          Accounts_Allocated:

          Load AccNo, ceil(Rank/$(vFactor),1) as AgentNo resident Accounts_Sorted;

           

          You will now have 20 agents allocated randomly to 12345 accounts, this can change each day!

           

          Is this what you need?

           

          Regards,

           

          Erica