This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
In the early 20th century a German mathematician named Lothar Collatz developed the eponymous Collatz Conjecture. This simple math problem states that any positive integer is subjected to (3n+1) if odd or (n/2) if even, then, regardless of the starting value, the sequence will always reach 1. This is a problem that has troubled mathematics for years as there is no logical connection between the value of the starting integer and the number of iterations it takes to get to 1. For example, integers 44,45,46 all iterate 16 times while 47 iterates 104 times before resolving to 1. The extreme variability in the number of iterations it takes to get to 1 based on the starting integer may remain unsolved until a new area of mathematics is discovered. Mathematician Paul Erdos has offered a nominal $500 prize for a solution and further stating “Mathematics may not be ready for such problems.”
All of that aside, the conjecture presents an excellent analog through which we can learn how to use Iterative Macros within Alteryx. The key to understanding an Iterative Macro is that data is passed through the macro logic and filtered to one of two outputs. Either the values of those records meet some specified criteria to be considered completed (in our case n=1) and passed out of the macro, or they require additional processing (n>1) and will loop back through the macro until the records have met the specific criteria. In the case of the Collatz Conjecture, does the value from n/2=1? If not, pass the resulting integer back through the macro.
The general logic in flow chart form:
In the beginning:
Starting this macro is like many other macros, with a Macro Input Tool. This tool allows you to pass data in to the macro and will also serve as the place where data will “loop” back to if it does not meet our criteria. Where normal Macros and even Batch Macros do not necessarily require a Macro Input Tool, it is essential for an Iterative Macro.
Best Practice Alert!
While developing a macro, the Macro Input tool serves two major purposes. The first is to create an input anchor for the macro which allows data to enter the macro when used in a workflow. The second function is inside the macro it allows you to create a custom table (via a Text Input Tool type input) or designate a specific file you would like to use as sample data to use as you develop the macro. In general, it is a best practice to use or create data in the format that you anticipate the macro to use in production. You can now build expressions using the fields and fieldnames in the sample data. In the case of this macro we have 2 fields a Seed field (containing the original starting integer), and an N field which will equal the Seed at first then change as the value goes through the iterative macro.
As you build macros and you should consider if you want to “Show Field Map” in the Macro Input settings. Selecting this option gives your end user the freedom to have fields named differently from your sample data. They can then “map” their fields to the field names used in calculations throughout the macro.
Since we know that the number 1 iterates 1 time, and since applying it to the Collatz Conjecture logic it will produce a value 3 iterations, we can create a basic look up table and use a Join Tool to address that issue. We’ll set a Text Input tool to have two fields (N and Iterations). One field will be called N with a value of 1 and the other will be called Iterations also with a value of 1. We connect this text tool to a Join along with our Macro input, and join by the field N. This is so when the value 1 is entered, it is immediately joined to the Text Input which can be set to Union with reported out of the macro, rather than processing it.
Now for the fun part!
Anything that does not join from the previous step will be passed out of the L or R side of the Join tool depending on which side your Macro Input field is connected to the Join tool. The next step is to determine whether our integer is now Even or Odd. To do this we can use a nifty mathematical equation called Modulo. In technical terms this operation will find the remainder after division of one number by another. For example, 7 mod 2 evaluates to 1 because 2 x 3=6 and 7-6 yields a remainder of 1, whereas Even numbers yield no remainder and therefore return a 0. This is a handy trick whenever you want to identify and filter your Odd and Even numbers accordingly. We use a Filter Tool to achieve this by using a the Mod function Mod([n],2)=0 which will pass any Even numbers out of the T and all Odds through the F.
As you can see in the above workflow the Modulo function will split the integer to go to n/2 if even or (3n)+1 if odd. Since the iterative macro is only dealing with a single value at a time in this particular problem, only one stream of data will be active during any given iteration following the Mod function. To keep the iterations going, any integer greater than 2 will need to go to the Macro Output renamed “I Ouput.” This is key, because this is the Macro Output tool that feeds the data back to the Macro Input for continued iterations. We achieve this using a Union tool. Similarly, to account for the number 1 being used, we will use a Union tool to connect the J output of the Join tool to the Macro Output tool (O Output). This Macro Output Tool symbolizes records that have completed their iterations (ie have reduced to 1).
Best Practice Alert!
When building an Iterative Macro it is generally the best to have the records leaving the I Output match the schema of the Macro Input, this way additional fields do not continue to get attached to the records through each iteration. The values in those fields can change as a part of the iterative process, however, the schema should ideally remain the same.
When the iterations reach a point where the integer is 2, then it will pass through the Filter where n=1 to the True side and gain a field called “Iterations” (via the Formula Tool) which will tell us how many iterations it took to resolve the starting integer to 1 before finally passing out of the macro as a “finished” record.
Configuring the Interface Designer
Now that we’ve set up the macro logic, and tested odds and evens to make sure the data passes the correct way through the logic, we need to tell Alteryx which Macro output will have records that need to loop back to the start for additional processing. You can access the Interface Designer by clicking on View >> Interface Designer (Keyboard Shortcut: Ctrl+Alt+D). This window allows you to control the appearance of the configuration window, test the macro or app via Debug Mode, view and edit all of the Interface Tools via a Tree view, and set additional Macro properties.
In constructing this macro I have chosen the “Show Field Map” option in the Macro Input tool. This exposes the two sample fields we’ve been using. When this macro is placed in to a workflow the drop downs will be populated with the fields from the data stream connected to it. Each element in the Layout view corresponds to a Question (List box, drop down, tree, text box, check box etc). These objects can be rearranged by selecting them (selected features have a blue dotted box as above), and moving them via the arrow keys on the right side of the window. Additionally, you can add labels (text blurbs for description), Tabs (for organization), and group boxes.
Test View allows you to insert values, and open the workflow in Debug mode to see how those values are being inserted in to the workflow. Not as useful in macro development, Debug Mode is mostly commonly used to troubleshoot Apps built in Alteryx.
For those of us who have been using Alteryx since the days before the Interface designer this view is a nostalgic window in to how Macros and Apps used to be built without Interface tools. That said, for anyone who has never seen this before this is a useful place to find all of your questions and actions in a consolidated location. This is particularly useful if you have a large app or macro with many questions spread out throughout the workflow.
The properties window is where the iterative macro all comes together. The section for Iteration Input and Iteration Output will only be available if the macro has been saved as the Iterative Macro type. You can set this by clicking on blank canvas, then choosing the Workflow tab in the Configuration window. Here you will choose the Radio button for Macro, and the drop down should be set to Iterative. It is also helpful to have named your Macro output tools so that you can easily identify the Macro output that is meant to go through the macro again.
Alteryx also has a built-in Maximum number of iterations to prevent your math or other iterative logic from getting stuck and iterating infinitely. The radio buttons let you control how your macro handles records should you reach the maximum iteration limit. Error, stops processing and throws an error, Warn, gives the user a yellow warning in the results window and outputs left-over records via the I output anchor exiting the macro, and Output leftover records gives you no specific warning or error and just outputs left over records.
Output mode is the final configuration option which gives the user some flexibility in how to handle the records as they move through the macro in terms of schema. Should you not expect any change in the number, size, and naming convention of fields, then the first option is sufficient. Should there be a chance that some iterations may only have 3 columns and others 4, then the second option is the one to choose. This will allow the data to pass through the macro, and output the two different tables as though they were passed through a Union tool and configured by field name. The third option is useful if you expect differing field names as data moves through the macro, but the data is the same. For example, one data stream may have the first field as “Address” and the second data stream is abbreviated “Addr.” The data in the field are the same, and fields are in the same position, so choosing the 3rd Output Mode option will cut out the need to rename fields you know to be the same in terms of position and contents.
Ever wanted to have some fun with icons for your custom macros? For the artistically inclined the icon size is set to 41x41 pixels from there feel free to design any image you like! Those of us who are less artistic gifted, Google Image searching can yield some excellent options. After entering your search term, click on the “Tools” option, set the size to “Icon” and “Usage Rights” to “Labled for Reuse.” This will provide you with a list of free-to-use icons to help you distinguish your macros from the crowd!
The beauty of the Collatz conjecture is that it is easy to understand the math, but hard to understand why it behaves the way it does. Hopefully this exercise has taught you how data moves through an iterative macro, and maybe some intrepid Alteyrx user may set the mathematics world on fire with a proof!