What are Iterative Macros

Strap in team, this one’s a doozy. It’s been a while since I’ve written a post but I felt so strongly about iterative macros that I had no choice but to come out of retirement – and by I felt so strongly I mean I don’t have a clue what’s going on and so I’m blogging about them to improve my understanding. Iterative macros are a broad topic and so for the sake of the intro I’ll give some general pointers, but to go in more depth I will be using Alteryx weekly challenge 12 for reference, so feel free to give this one a miss if you care about spoilers.

In general, iterative macros are macros that will keep running until a certain condition is met, maybe we’re summing two things and the macro will keep looping until the total is over 1000 etc. Therefore, they have two outputs, one which is used for looping and the other which displays the output. N.B. Unlike the other type of macros there is no tool which automatically converts the workflow to an iterative macro – the way to do this is on the workflow menu. That’s some general context for iterative macros but for more detail, let’s have a look at challenge 12.

Challenge number 12 please

For context, we are given a list of employees and there managers – as shown below.

What we want to work out, for each employee, is a hierarchy displaying, the relationship between all employees e.g. The hierarchy from analyst to director is 2.

General good practice with macros is to get the solution working for one case, and then adapt it to have it work for all. In this case we’ll look to get all the combos where the hierarchy is 1, and then adapt to get the rest.

The best way to do this would be to join the input table onto itself so that we can display both the employee and the manager on the same line. This can be done using the join clause below

Although it is possible to join the same file onto both anchors of a join clause, for reasons we will discuss later it is better to use two different input files that are both showing the same thing

Doing so should leave us with a table that looks like the one below

In this case the Right_employee is the manager of the employee, and so the Right_man_id is the employee’s manager’s manager (sounds confusing but will be crucial later). N.B. We’ve lost a row but that is because the CEO doesn’t have a manager and so has nothing to join to (again will be useful later).

Just for cleaning purposes we can use a select tool to leave us with just the employee and Right_employee (manager). Then we can use a formula to tell us what the hierarchy is between each employee – remember in this single case we were looking for all the values to have a hierarchy of 1.

The calculation we use to get this is quite interesting. When we have an iterative macro, a special variable becomes available called [Engine.IterationNumber], which tells us how many times the macro has run. The first run however, is run number 0, so we will add 1 to it to give us a hierarchy. The hope is that as the macro keeps running the iteration number will go up and that’s how we get our hierarchy.

This is a great time to check that the workflow is set to an iterative macro as this calculation won’t be possible otherwise.

Doing all of this should get us an output that looks something like this

Just remember that for macros we need macro-outputs so the last few steps will look like this on the flow. Also now’s a good time to change the inputs to macro inputs. I labelled the one going to the left join I as this is what the iterations will replace, and the one on the right anchor F because this is fixed.

This macro-output is labelled as O since this will be the output, the next step is to work out how to make it iterate (create the iteration output labelled I).

For the iteration output we need to create some sort of condition that allows us to iterate, and in this case that condition will be the join i.e. We will keep joining until we can’t join anymore (here’s where the join not working for the CEO becomes important).

In our first join for the output, we joined the employee to the manager, and that was how we got the everything we needed for comparison. Now we want to do the same thing, but this time we join the employee to the managers manager so that we can get to the next level in the hierarchy. We do this by joining the employee to the Right_man_id.

The obvious tell that we joined id to Right_man_id is that Vice President’s man_id is null, which was originally the CEO’s man_id.

It’s important that Right_man_id  is renamed to man_id so that the output schema we use to iterate on matches the input that we will be replacing.

Finally add a macro-output labelled iteration and as far as the workflow is concerned, we are finished.

To get the iteration right we need to go on the interface designer. This can be found in the view tab at the top of Alteryx.

If everything has been named properly the setting should be obvious.

Now save this as a macro and on a new flow load in the macro and add in two versions of the challenge input file.

If everything’s gone well you should get this answer. Don’t worry about the D output that was just so I could check something.

Then some sorting to give you the answer that the challenge wanted.