Hi Everyone.
I've been struggling with this problem recently and I thought you could help me out.
I have 2 lists (employee list and job list) and I want to allocate each job in the job list to one of the employees. There are two conditions I would like to achieve simultaneously when allocating the jobs.
Employee List
Employee Name |
Employee 1 |
Employee 2 |
Employee 3 |
Employee 4 |
Job List
Job Code | Job Price |
J1 | 100 $ |
J2 | 28 $ |
J3 | 34 $ |
J4 | 63 $ |
J5 | 88 $ |
J6 | 13 $ |
J7 | 184 $ |
J8 | 19 $ |
J9 | 53 $ |
J10 | 66 $ |
J11 | 111 $ |
J12 | 96 $ |
J13 | 81 $ |
Condition 1: I want each employee to have allocated similar number of jobs. I want to minimize the variance between number of jobs allocated to each employee.
Condition 2: I want each employee to have allocated similar amount of Price, I want to minimize the variance between Price allocated to each employee.
I can tile it by sum amount or equal records with Tile Tool, but I want both of these conditions to be met simultaneously. Should I try using Optimization Tool?
Thank you for your advices in advance.
Solved! Go to Solution.
@o-yasinakpinar
I seems to be a Bin packing problem, but you also limited the number of bin, wihch is a bit tricky.
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/HELP-How-to-use-ITERATIVE-MACRO/m-p/92...
I have given a try but honestly probaly not perfect.
Thank you for your solution. I appreciate it. Imagine Job List to contain tens of thousands of jobs and employee list hundreds of employees. Although it can be a shortcut solution, what I want is to find the most optimal solution not just any solution which satisfies the constraints. I believe it can be achieved by multi-objective optimization or goal programming algorithms.
@o-yasinakpinar
I thiink you are right.
Optimization is mostlike to be an iterative process and requires large dataset for calbrication.
Thank you for the feedback.