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.
Under the hood of Alteryx: tips, tricks and how-to's.
Alteryx Gallery is experiencing a problem in which system emails are not being sent out. As a result, if you are attempting to sign up for a new account, you may be unable to verify your email address. We are working to solve this as soon as possible and will remove this notice once resolved.
In my last article in the series, we went through the basics of creating a macro and utilizing the interface tools. Also, if you read that blog, you know what is going on and I don’t need to do introductions so let’s just jump right in.
(If you haven’t read it, you should.)
We will start off with a new definition to get us ready for the read ahead:
Loops: sequence of instructions that is continually repeated until a certain condition is reached. Now there is nothing in Alteryx that explicitly calls an Iterative Macro a loop, but that's what it is. We are taking your dataset and applying logic to the same records over and over again until the loop closes.
Okay back to NASCAR. We discussed standard macros, and how we are only taking a single lap, but I have watched my fair share of events and I have never seen a race that lasts only one lap. Each lap is another iteration on the same car, that being, every lap is the same path and the same process, but every time you go around the track, something is different with your car. There is less fuel, the tires are respectively worn down, and air pressure has adjusted the shape of your car ever so slightly! To put it in different terms, you are driving a car that is a little bit different on every lap and at the end of 70 or so laps, your car performs much different than it did on the first lap. Essentially this is what is happening in your iterative macro. You have your data looking a certain way and then run it against the same conditions over and over again until your criteria are met.
This is where the looping terminology that I mentioned earlier comes into play. When programming in a coding language like Python, loops are super valuable in order to create processes that continue to run iteratively, as long as a series of criteria is met. For example, if you are using Python to do web scraping of comments on a website, you may want to see more than one page of comments, as the initial code would only pull the first page. Meanwhile, with a loop, you can have the process happen over and over again until you have met an exit criteria, let’s say for our example, ten pages.
The easiest way to demonstrate what is happening is to look at an early example on the Alteryx Weekly Challenges #12. In this example, we have a series of records where you have an employee title, an ID to the title, and the number of the managers’ title ID.
The challenge is to see in the output, how many steps any one position has from all the positions above it.
Many of you reading this are probably beginning to go through this process in your heads. First, we need to join the data to itself on the ID; then we need to join it to itself again on that new manager ID and so on until all records reach the CEO position. If you look through the solutions, you see that many people solved the problem in the manner below.
If we just simply look at all of these black boxes, we can see that we have manually built the same process four times. For this example, that is acceptable, but when was the last time you had an organizational hierarchy with 4 layers? What if we have a hierarchy structure with 40 layers? You would have to create this process 40 times in your workflow to accommodate all the layers. In the same vein, what happens if we add an SVP title between VP and CEO? Your results would be incomplete because there would now be a fifth layer to your process. Here is where an understanding of Iterative Macros comes into play.
Though I am sure that I have done this challenge a few times, I couldn’t find any of my posts about it, so I will borrow from my fellow ACE: Gary Gruccio. In Gary’s workflow, we see a different set of tools with an Iterative Macro in the middle.
If we dive into this macro, we see that it looks super simple, and actually mimics the steps shown above.
So you might be asking yourself, “Self, what are we not seeing here? Why is macro able to produce results as if it runs many times, but we only see it running once?” Well, these are great questions, and I remember standing in your shoes. We are now starting to get outside of traditional Alteryx workflow and inside macro creation. Until now, we have been able to see and manage everything that is happening on your workflow canvas. We know that this process needs to run in an untraditional manner, so we are going to tell Alteryx exactly that.
Make sure you first have your canvas selected, then look over to the “Workflow – Configuration” panel. Here you will see a series of tabs. The number of tabs here will be determined by whether Alteryx has already determined if this workflow is a macro. We have Macro Inputs/Outputs in our workflow so we have four tabs. If you select ‘Workflow’ you will see a neat little subsection of workflow options that you may have glossed over before. You see that there are options for “Standard Workflow,” “Analytic App,” and “Macro.” In the macro drop down we see a few options:
Standard Macro (previously discussed)
Batch Macro (more to come on this later)
Iterative Macro (MONEY)
Location Optimizer Macro (I have never seen this and have no clue. Dear Alteryx, what is this?)
For this exercise, we want to select “Iterative Macro.” At the surface, it may seem that nothing has happened, and these settings can be quite confusing to the unknowing analyst. Luckily, we will soon be trained analysts and thus know where to look for some very important configuration options have made themselves known. If you navigate to the Properties Icon on the Interface Designer tab, we now have some neat new Iterative Macro related settings that we are going to use to create magic!
Let’s go through these one bit at a time, but slightly out of order.
Iteration Output: If you look at our macro, you will notice that there are two “Macro Output” tools. When Alteryx runs a loop through an Iterative Macro, you need to push data out in two ways. The primary is going to be a results output. This is where the results of each pass through will be translated out onto your Alteryx canvas. The secondary is going to be your “Iterative Output.” This is the data that is going to be run back through your workflow on the next loop.
Let’s get Visual!
Loop 1 has two identical data sources that you push through all of your Alteryx processes, which will give you two outputs. Your results will be (A), the raw output of our join, and (B), your iterative output, which will have a few changes made to it (this is important).
Loop 2 now looks slightly different. You have your input data (A), which is how your raw data looks coming into the macro from your workflow. The difference now is that you have input data (B) with the results of your Iterative Output from Loop 1, which is now processed through your second loop the same way that Loop 1 was run.
This brings up our second setting, which is the “Iterative Input.” This is the input that we will be using to bring the output of your Iterative Output through on the next loop. Clear as mud right?
Maximum Number of Iterations: Typically, in your macro, you want to build an exit strategy so that you can get out of the continuously looping process that is your macro. This is usually done by slowly dwindling down data that is output in your Iterative Output until there is no new data left to process. However, because of the sometimes volatile nature of data, you can get caught in loops because criteria are not met that would allow you to exit. This interactive menu allows you to create a contingency plan for exiting your iterative process and keep it from running into infinity. *Side Note: If you want to have fun crashing your computer, the “Generate Rows” tool works in a very similar manner to an iterative macro. If you don’t give it a valid close logic, you can create records until your computer has completely used its memory and violently goes to sleep. I may or may not have done this before.
When Maximum Number of Iterations is Hit: What do you want to happen if you hit that desired “Maximum Number of Iterations”? Usually, you hit that maximum number of iterations because something went wrong. I always ask for an error message, because the output of my macro will most likely not work for me if we get to this point.
Output Mode: The results output of your Iterative Macro works the same as the “Union” tool, in that every iteration just stacks new records on top of each other. Manage this in the same way you would manage that; you know your data better than anyone.
Once your macro is successfully configured, you should be good to drop that right into your workflow. I would suggest running through a handful of daily challenges and attempting them yourself as well as digging through other people’s solutions in order to see the different ways to manage the flow of your Iterative Macro.
One other thing to note is that when you are debugging your macro, it can be difficult to envision how future iterations will interact with whatever your data looks like at that point. On the Interface Designer there is an option called “Test View” and it looks like a magic wand. Here you can “Open Debug” and it will create a separate workflow that is essentially your macro, but without any of the interface tools involved and will show you exactly what your workflow will look like. Read up on that topic here. (I can’t find any really good blogs on this. Maybe someone should write one!)