Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

darrin_pilkingt
Contributor II

How to evenly distribute random values to agents

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.

1 Solution

Accepted Solutions
Not applicable

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

3 Replies
Not applicable

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

darrin_pilkingt
Contributor II

Re: How to evenly distribute random values to agents

Erica,

Of course I was making this much harder than it needed to be. 

Thank you very much.

Not applicable

Re: How to evenly distribute random values to agents

No worries!


Another way could be to use a random number * 20 (or number of staff) for each row of accounts. This would be quicker to load, but would of course not guarantee an equal number of accounts per staff. Situation dependent I guess.


Erica