A few weeks ago, DSAU2 worked on a client project that involved architecting a dashboard for the future. Part of this was using dummy data to populate the view. Pulling together a combination of Alteryx Designer tools — including multi-row formulas, unique tools, formulas, and record IDs — I was able to string together a set for us to you. But imagine my surprise when Neal showed us this week that the same could be achieved using an iterative macro!

What is an iterative macro?

An Iterative Macro will run through every record and then loop the records back through the workflow, repeating the entire process as many times as is specified, or until a condition is met. – Alteryx Designer documentation

In other words, it’s a macro that will loop the workflow within until you tell it to ( loop a set amount of times or until a condition is met).

How to build a dummy data generating iterative macro?

Imagine we’re making 20,000 record dummy order data for a souviner shop. Here are the dimensions and fields they want:

  • Product: Plush toy, Keychain, Post card, or Boomerang
  • Order ID: Integer ranging from 1 – 10000
  • Order Date: Date ranging from 1/1/2019 – 28/2/2019
  • Total Price: Integer ranging from $5 – $1000

Step 1: Tool 1 – Macro Input

Starting a macro is easy. I like to use ‘Text Input’ option and simply ‘1’ into the field. Given that we will hardwire the values later in the next tool, it’s ok to put a placeholder as I did.

Step 2: Tool 2 – Formula Tool

The formula is where we will create all of our dummy data. The best way to create categorical dummy data on Alteryx is to create a random number for each product and then assign a category to each number. In this scenario there are 4 products, thus generating a number between 0-3 and assigning names in the following formula.

Product Formula

RandInt(3)

Product Formula (Update)

IF [Product] = ‘1’ THEN ‘Plush Toy’
ELSEIF [Product] = ‘2’ THEN ‘Keychain’
ELSEIF [Product] = ‘3’ THEN ‘Post Card’
ELSE ‘Boomerang’
ENDIF

Order ID

RandInt(10000)

Order Date

1+ RandInt(27)

Order Date Month

1 + RandInt(2)

Order Date

tostring([Order Date Day]) + ‘/’ + tostring([Order Date Month]) + ‘/’+ ‘2019’

Total Price

5 + RandInt(9995)

Total Price (Updated)

‘$’+ToString([Total Price], 2,1)

Total No. Of Orders

50

Step 3: Tool 3 – Select Tool

Here, I de-select my ‘Field 1’ placeholder, ‘Order Date Day’ and ‘Order Date Month’ fields as I no longer need them.

Step 4: Modify the macro type to ‘Iterative Macro’

In Workflow – Configuration, I now change the Macro type to an ‘Iterative Macro’. Note how changing the macro type will automatically generate a new Engine type named ‘IterationNumber’ with a 0 value. This number is a count of how many iterations the macro has run. However, the first iteration will always start at 0.

Step 5: Tool 4 – Filter Tool

Leading on from the point above, we would now need a filter to determine whether data is to be outputted or requires running through an iteration again. In this scenario, given that we want 50 rows of dummy data, our filter logic is that if the workflow has been run 50 times already, output the data. Else, keep looping the workflow.

Remember how each Engine.IterationNumber starts at 0? You would want to add one to it to correctly track the number of iterations.

Step 6: Tool 5 – Union Tool

The union tool is used here to append each record of data into one stream. You would join up the false and true outputs to the union tool and use the default settings.

Step 7: Tool 6 – Macro Output

Your Iteration output is the data to keep looping. Make sure to give your Macro Output a meaningful name so you don’t confuse yourself later!

Step 8: Tool 7 – Macro Output

Likewise, your other Macro Output would be for outputting compiled macro data. Again, make sure to give it a meaningful name.

Step 9: Designer Interface

Now you have a workflow ready, you want to go into Interface Designer to modify its configurations. The meaningful Macro Input and Macro Output names will now come in handy as you identify the Iteration Input and Iteration Output. I’ve chosen to use the Auto Configure by Name output mode because in the case of a mistake, with this setting Alteryx will (similar to a union tool), automatically try to configure the output.

Step 10: Save macro

Macros in Alteryx are saved as a yxmc file. Make sure you save your macro before running it to ensure all changes are wired into it. A good way to check whether a save is done is to see whether an * is after the file name in the tab. An * means changes were made but not saved. No * means all changes thus far have been saved.

Step 11: Run macro in a new workflow

Now your iterative macro is ready, test it out by opening a new workflow, attaching a text input in the same schema as the macro input, and connecting it to your macro. In the results window, if your macro is working, it should list how many iterations were run. As you can see below, I received a message saying 49 iterations were run and I have 50 records. Remember the first loop is automatically numbered 0 by Alteryx so as long as you have the correct amonut of records in the output itself, you should be fine.

Pris Lam
Author: Pris Lam