Alteryx Macros - How to build an Iterative Macro in Alteryx

by Jesus Esquivel Roman

This blogs outlines how to create an iterative macro for the Alteryx Challenge #12.

Figure 1: Input
Figure 2: Desired output

Step 1: Identify steps and tools to repeat

For this challenge, a join and select (which can also be done inside the join) are tools to repeat to get the desired output (Figure 2). Using the same input table (Figure 1), join the manager id (man_id) with the employee id (id) to get the employee's superior. This will make the initial 3 fields from Figure 1 into 6 fields as you join this data. If you repeat these steps for a few times you will get to a result closed to the final one, but this is not the most efficient way for this or similar exercises.

Figure 3: Repeatable steps

Step 2: Create an Iterative Macro

Open a new workflow > drag input tool to use (you can copy and paste the original input) > convert to macro input > save workflow (it will automatically save as a macro or .yxmc file).

In the canvas Workflow change the type to Iterative Macro.

Figure 4: Changing Type of Macro

Step 3: Build the Iterative Macro

For an iterative macro, you need both an iterative input and iterative output (at least in this example). First, you will need to identify which iterative output will replace your iterative input as well as your final desired output. Note that the iterative output must have the same structure as the iterative input.

Place the first batch of repeated tools built earlier (i.e. join). For this example, join the manager id with the employee id. Only keep the employee, employee id, the manager (employee from the right join which represents the superior), and the manager id.

The y output represents the iterative macro. Only the employee, employee id and manager id go to the iterative output (y output) because it needs to have the same structure as the input. The select tool before the y ouput allows us to select these three fields only. The y output will replace the iterative input (see step 4) and repeat the step until no further data goes into the join.

Figure 5: Alteryx Challenge W12 Macro Workflow

To know how to configure the iterative output and iterative input check the following step 4.

Step 4: Configure macro interface in Macro Interface Designer

Open the Interface Designer by going to View > Interface Designer and then navigate to the properties as shown in Figure 6.

Select the iteration input and output as shown below and change the output name to auto configure by name (might be different for other examples).

Figure 6: Interface Designer - Properties

Step 5: Configure Final Output

For the final output you only want the employee, manager and hierarchy (the select tool before the final 'z output' allows us to select the employee and manager only). For the hierarchy value, you can create a new column with the following formula [Engine.IterationNumber]+1.

Figure 7: Alteryx Challenge W12 Macro Workflow Tools

Step 6: Input the macro to the Main Workflow

The final step will be to input the created macro into the main workflow, and add additional tools to obtained the final desired output as shown below. You should only connect the final (z) output to the succeeding tools.

Figure 8: Main workflow final output