Creating Iterative Macros in Alteryx

Introduction

This tutorial is a continuation on the previous posts on the various types of macros in Alteryx, as well as, creating standard and batch macros in Alteryx.

This time we will move on to iterative macros. To recap, an iterative macro allows you to loop through data multiple times until a specified condition is met. In this tutorial, we will use the same YouTube data from last time, but this time we will calculate the engagement ratio for each year and stop loading files when a specified ratio is reached.

Step 1: Download the Dataset

Download the dataset from this link. If you followed the previous tutorial on Batch Macros, you may recall the data is separated into different years. Instead of loading each year individually, our Iterative Macro will process them dynamically.

Step 2: Prepare the Data Extraction

Text Input tool. Add a single field called FullPath with the full path as a string to the youtube_2005.csv. To make it easier, this can be copied and pasted in from a Browse tool or your file explorer.

Connect the Text Input tool to the Dynamic Input tool. Set it up the same as the previous tutorial.

Step 3: Select the Fields and Convert Data Types

For this macro we will use the Category, Video views, Likes, Dislikes and Published columns.

Use the Data Cleansing tool to remove the punctuation from the Video views, Likes, and Dislikes columns.

Use the select tool to select the previously mentioned fields. Additionally, change the datatypes for Video views, Likes, and Dislikes to Integer. Rename published to Year.

Step 4: Calculate Engagement Ratio for each Category

Use the Summarize tool to sum all the likes, dislikes, views for each year as follows:

Use the Formula tool to create a new field called “Engagement Ratio”.

Step 5: Setup Engagement Ratio Filter

Add Filter downstream of the Formula tool. Set this tool up for the condition Engagement Ratio <= 0.01. This will be the cut-off for when to stop loading files.

Step 6: Append FullPath the Output

Connect the true output anchor of the Filter tool to the source (S) input anchor of an Append Fields tool. Connect the first Text Input tool to the target (T) input anchor of the Append Field tool.

Step 7: Update FullPath

Connect a Formula tool to the output of the Append Fields tool. Update the FullPath with the following code to increment the year:

REGEX_Replace([FullPath], ToString([Year]), ToString([Year]+1))

Step 8: Add Macro Outputs

Connect Macro Output tools to both the true and false output anchors of the select tool to capture the matches up to the final cut-off (true) and the final output (false).

Additionally, add a Macro Output tool to the Formula tool. This serves as the output for each iteration. Reposition it on the canvas to be near the Text Input tool to make this clear.

Right click on the Text Input tool and convert it to a Macro Input. The result should look similar to this:

Step 9: Convert to Iterative Macro

Goto the Workflow Configuration and change the Macro Type to Iterative Macro.

Open the Interface Designer and go to the properties section. Change the Iteration Input and Iteration Output to what your Macro Input and Macro Output are named. It is helpful to rename these to make them easier to find.

Save the Iterative Macro as IterativeEngagementMacro.yxmc.

Step 10: Use the Macro in a New Workflow

Open a new workflow, right click on the canvas and insert the newly saved macro.

To the Input connect a Text Input tool containing the FullPath to the first file to load. Additionally, connect Browse Tools to all output to inspect the results.

Conclusion

In this tutorial we worked through the process of creating an iterative macro in Alteryx. Additionally, we used the macro in a new workflow to test its functionality. This was the final part in the Alteryx Macro series where Standard Macros, Batch Macros, and Iterative Macros were covered.