iterative macro for copying data w/o a join

[kcsuhaj]

Hi All

I have a problem, which I managed to solve w/o using a loop (iterative macro) and it works fine, still I think with a macro it would be much more simple/elegant (especially that there are some more conditions to consider).

I have 2 datasets: list of tasks and list of employees with availability. The goal is simple: assign 1 employee to each task, considering the availability limit.

In the current solution I am calculating the total number of tasks for each employee based on the capacity, generate rows with each employee ID as per the capacity and joining the datasets with record ID.

What I am trying to achieve with iterative macro: loop through each record of the task list (with the help of [Engine.IterationNumber] ), copy the first employee ID where the capacity is > 0 to the Assignee column and decrease the total capacity of the given employee by 1 in the employee table. Where I struggle is the copy of the value, not sure which tool should be used for that, as I have no condition to do the join on..

As per the sample data the tasks/FTE = 5, so the desired output is: E001 and E002 should be assigned to 5 tasks, E003 to 4, E004 to 3 and E005 also to 3 (due to rounding w CEIL)

Any ideas pls?

employeeID	FTE
E001	1
E002	1
E003	0.8
E004	0.5
E005	0.7

Task_ID	Assignee
123-001
123-002
123-003
123-004
123-005
123-006
123-007
123-008
123-009
123-010
123-011
123-012
123-013
123-014
123-015
123-016
123-017
123-018
123-019
123-020

[MatthewO]

@kcsuhaj attached is an approach to this without using a macro. It calculates the number of tasks an employee can be assigned, and a row is generated for each task. This data set is then joined to the Tasks using the "Join by Record Position" configuration.

[michelle_mathews]

Hi @kcsuhaj , I created an iterative macro using your data (attached) which works as expected, though I think the solution proposed by Matt may be much simpler! 

[kcsuhaj]

@MatthewO: many thanks for your reply. As described I have the (almost) exact same solution working already in place

@Michelle: also many thanks for your reply, I cannot open it for some reason (probably I am using an older version of the tool) and I see only this: 

[MatthewO]

@kcsuhaj can you clarify why you would prefer to use a macro if you can achieve the result without one? I agree that there is an iterative component to this use case, but this is addressed by the Generate Rows tool. This tool will “loop” until the configured Condition Expression is satisfied.

[kcsuhaj]

For 3 reasons:

1. there are certain priority rules, based on which the assignment of the cases should happen. So my workflow does first the assignments for the matches found based on these rules and after each assignment round it is recalculating the remaining capacity of each employee. As the number of rule-based matches is dynamic, it might happen that it assigns more tasks to certain employee than the capacity. This issue I wouldn't have with a proper loop-based solution

2. all rules (5 rules currently) are coming with input files, finding the matches, append, generate records, join etc the workflow looks like a forest. My expectation is to have significantly less tools used to make it work with a macro

3. I am willing to learn to build a macro, which should be nicely iterating through the records, I just don't know how can I fill the value, using another source if there is no join condition available, but it must be possible