Generating a List of Random Numbers with an Iterative Macro

[ChristineB]

Iterative macros can be used to repeat a process a desired number of times. Say, for instance, you want to generate a list of 10 random numbers. Rather than create ten different formulas for the values, wouldn’t it be great to simply tell Alteryx how many values to create, hit “Run”, and voila? Well, with a few clicks to create a simple iterative macro and some knowledge of engine constants, you can! With the particular examples provided in the sample workflow, I’ve imagined two tasks. One is to generate 10 random numbers between 0 and 100. This input data only requires the number of values to output (Figure 1, A). The second task is to create a number of values associated with a group. In this case, the input data has two fields: the Group Name and the number of values to create for that group (Figure 1, B). The input data feeds directly into the iterative macro which begins the process of repeatedly generating random values the specified number of times.

Figure 1:Input data for the two sample scenarios

The number of times the macro repeats the process of generating a random value is controlled by two things: the user-defined value of repetitions in the input data and the engine iteration number within the macro. Once a workflow is defined as an iterative macro, certain constants are then made available for use within the workflow (Figure 2). For example the engine iteration number, a constant which records the number of times a macro has run, is a usable field in the Formula tool (Figures 3). As a usable numeric field, I can insert the engine iteration number in a formula to record the number of repetitions needed to generate the values I need. Because the number of Engine iterations starts at “0”, adding “1” to this value scales it appropriately to the value(s) entered in the input data.

Figure 2: Iterative macros activate engine constants, and specifically the iteration number, for use within workflows. Note that iteration numbers begin at 0

Figure 3: Engine constants can be used to build expressions in the formula tool

This engine iteration number helps control the number of times a repetitive process runs by serving as a logical comparison in a filter tool: does the number of values I set out to create equal the number of times the macro has run? In other words, does my input data value equal my engine iteration number +1? If the answer to that question is “no”, then the workflow will run again after storing the data it just created using a union tool. If that logic’s answer is “yes”, then the process has generated the desired number of outcomes and leaves the iterative macro, along with any previously generated data that has been unioned from previous iterations (Figure 4).

Figure 4: If the number of desired values has not yet been created (per the logic of the filter), then the process of generating values will repeat. Regardless, the random values that are created are stored with a union tool and output once the Filter's logic is "True"

After leaving the macro, the data can be summarized to visualize grouped data, selected for certain fields, or even fed through Data Investigation tools for further analyses (see attached v10.6 workflow).

[LoganC]

Hi Christine, thanks for this blog - really helpful to understanding iterative macros. I have come across a similar use case but with one additional complexity. Have a prize raffle drawing, however if a person wins, they cannot win a subsequent prize, therefore I would like them to be removed from consideration of a 2nd prize. (ideally running the workflow all at once to pick all winners).

I have tried using a batch macro but have not been able to determine a way to essentially loop back through if a duplicate name is selected. I think an iterative macro might be the right approach but don't have much experience with them, and haven't been able to piece it together. 

I believe I almost need each iteration to be output after 1st record, then essentially dynamically removing it from the list to be selected from. A bit of a one-time use workflow but trying to use it as a bridge for iterative macros introduction. Thanks in advance for any advice.