Allocate clients to agents

[HeinL]

Hallo Alteryx Community

I have an allocation problem that I need your help with.  I need to allocate clients to agents based on a couple of things.  The following is an attempt to describe the two data sets and to hopefully highlight the problem.

Client data:

1. Client – unique key per client.
2. Mainplace – a geographical key. This key describes a geographical area similar to a town.  This is the location where the client lives.
3. Value – a monetary value. The higher the value the better.

Agent data:

1. Agent – unique key per agent.
2. Mainplace – same as the client. This is the location where the agent works.  An agent can work in multiple locations.  The see clients at work or at home.  Each Mainplace can also have multiple agents linked due to agents working in the same Mainplaces as other agents.
3. Score – a score made up of a number of things. It shows mainly how the agent is doing in that location.  This means an agent can have a different score per Mainplace.

I must allocate the clients to agents in the following way:

1. Clients must be allocated to agents working in the Mainplace where the client lives. This means each client can have multiple agents linked on the first pass (see point 2 in the agent section above)
2. The client with the highest value overall must be allocated to the agent with the highest score in the Mainplace.
3. On the second pass the next highest value client must be allocated to the highest scoring agent in the Mainplace. Here comes the snag.  The agent in the first pass must not be eligible after the first allocation. This agent must be removed from all further allocations.  If this was not the case a simple loop through the clients would have worked. 

This will ensure that each agent only gets allocated one client. So once an agent/client record is created that agent must be removed from further allocation.  The client will automatically be removed because a client only has one occurrence.  If any clients are not allocated at the end those clients must be written to a different file.  These records can then be run through the process again for allocation.  On the second run of the process an agent can get more clients.   Otherwise the one client per agent stands.

I uploaded a workflow with a very simple sample of the data.  The Browse node will show the client records with all the agents linked for the first pass.  From this point onward the loop must allocate as described above.

Thanks in advance for any assistance.

Hein

[Inactive User]

Please find solution attached.

[HeinL]

Hi Ryan

Thank you for the workflow.  I had a look at the process and the final output.  It is nearly what I want.  I attach an Excel workbook with the clients and agents matched on Mainplace.  The sheet shows all clients linked to all relevant agents for the mainplace.

In the sheet I color coded how the assignment must happen.  Your process does very well but fails on the first and best client in the group.  

So the assignment must start with the best client (highest value) and highest scoring in the mainplace.  Then both client and agent must be removed from the process for the next assignment.  An agent cannot be assigned again.

You will notice in the sheet that there are 2 clients at the end that are not assigned.  The aim is to throw them back into the process for assignment.  This will mean that some agents will then get more clients.  If the first pass assigns all clients then a second pass is not needed.

I hope this gives a better view of my problem

[HeinL · Accepted solution]

I solved my own problem with an iterative macro.  The macro allows me to keep track of the agents already assigned while I loop through the records.  This way an agent can be assigned only once.  At the end of the process I just join the full client list with the assigned client list to determine who was not assigned.  These records will just go through the process again.

Thanks for the assistance.