Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

iterative macro for copying data w/o a join

kcsuhaj
5 - Atom

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?

 

employeeIDFTE
E0011
E0021
E0030.8
E0040.5
E0050.7

 

Task_IDAssignee
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 
5 REPLIES 5
MatthewO
Alteryx
Alteryx

@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.

 

image.png

 

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
5 - Atom

@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: 

 

kcsuhaj_0-1674575495221.png

 

MatthewO
Alteryx
Alteryx

@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
5 - Atom

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

Labels