Introduction

In this blog, I will give a recap of how I approached Alteryx challenge #240 where we had to apply an iterative macro.

What is a macro and an iterative macro?

A macro in Alteryx is a custom workflow that you can reuse across different projects. Macros allow you to automate repetitive tasks and simplify complex workflows by combining multiple tools into a single reusable component.

Iterative macros are like while loops in pseudocode. It will continually run a workflow until a certain condition is met.

Challenge 240: The problem

In this problem, we have a data set where 99 people have ranked their 4 most favorite ice creams out of a total of 5 flavors. Using Ranked Choice Voting (similar to Australian politics), we are to determine what is everyone’s preferred ice cream.

For those who don’t know, ranked choice voting is a voting system in which voters rank candidates in order of preference instead of selecting only one candidate. The candidate with the fewest votes is eliminated and their votes are redistributed to the remaining candidates based on voters’ second choices, and the process continues until one candidate has a majority of the votes.

The Data

The ice cream flavours are as followed:

The preferences for each voter is shown below:

Cleaning:

First, we need to clean the data into separate columns using basic cleaning tools:

Creating the macro:

Now we need to create a macro in a new window that iterates this ranked-choice voting process. It will need to do the following:

  1. Count up all the 1st choice votes and find the mode
  2. If the mode is greater than 50% of total votes, we have a winner ELSE we need to eliminate the least voted and repeat the process

Due to the complexity of the macro, I will provide the whole workflow with comments. I will then have additional comments and pictures to aid the explanation.

A: This is a macro input, and it is a good practice to copy the schema of the cleaned data. above Therefore the macro input should look like this, and the macro will only operate if the number of columns is the same as what it is expecting.

B: After the summarize tool takes the min and max, we need to check if there is a majority vote. The input anchor here is shown

In this case, the first iteration is unsuccessful and so the min count of 6 will need to be joined and eliminated in step C

C: The formula that shifts all the cells will be as followed

D: When no lowest is found, it will stop feeding into the true anchor. This is important because in my first iteration of this macro, the iterative output was still receiving information when the finished output had already finished. This meant that the macro would iterate indefinitely

E: Output anchor shown below before being iterated again. Note that it must be in the same schema as shown in A.

F: Once the condition is met the final output can be in any form. In this challenge, all that was required is the most popular ice cream, the number of votes, and how many iterations it took.

Finally to configure this macro, we need to specify the following:

The Final Workflow

The result after joining and cleaning  is “In Round 4, with 56 out of 99 votes, the winner is Chocolate” 😊

Nam Nguyen
Author: Nam Nguyen