Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Every sales pitch I have ever been on, without fail, talks about three things:
1) Get people out of spreadsheets
2) Look how repeatable this is!
3) *in a whispered voice* “Oh yeah, and you can utilize macros to make everything better!”
I remember first hearing about macros and thinking to myself "Self, we are going to tuck this term 'macros' away in your mind and cross that bridge when we need to solve a problem that can only be solved with a macro!" The problem for me was that I didn't know what a "Macro Problem" was. I learned a lot through my first year of using Alteryx but one thing that I struggled with in teaching myself was not how to learn, it was what to learn. I was running up against "Macro Problems" without even realizing it, and to be honest with the universe for a second, I didn't even really understand the power of batch and iterative macros until after receiving the ACE designation for about six months. Hopefully through some direction and a series of analogies, you can avoid wandering aimlessly through the Alteryx wilderness as I did.
So what is a macro? A quick google search defines a macro as:
Macro (computing): a single instruction that expands automatically into a set of instructions to perform a particular task.
To put this in Alteryx terms, we are going to create a single tool that does the work of many tools.
It’s also important to understand some things on the interface tool palette. If you have always wondered what those are, this is the article for you. The interface tools are how you can create logic that the macro follows when running. Here are the main tools that we will be utilizing:
The tools mentioned above working together is where the behind-the-scenes magic happens. Most of the other interface tools are how you build out the way in which the user interacts with your macro. For example, if you are building a macro where a formula is applied to a field, you would use a list box to select the field we are working with. Not all macros have an interface, but most will. We won’t go into the detail of how each of these work, but here are some links to Alteryx documentation that help you dive in:
Now that we are 500 words into this thing, let’s talk about standard macros.
Actually, first let’s talk about me. One of the biggest things in my life right now is that I am trying to grow a mullet, because I want to look like Brock Hekking. It also makes an easy transition over to a NASCAR analogy. In NASCAR you have a track, typically a giant loop and a car. There is a lot more involved with the car, but for the most part, we just care about the car. In a standard macro we build out a process (track) and every time we approach that process, the data (car) is going to look relatively similar and thus should perform the same way on every pass. We know what everything looks like at the beginning, we know what everything is going to look like at the end of a single lap, which is exactly what your macro is doing. Data looks one way going in and a slightly different way coming out.
My first "Standard Macro" was five tools including the input, output, action, and select box tool. The only place where my data was actually being changed was the formula tool applying the conversion formula to the required field. I used to do a lot of work over in the AS400 space and in that particular instance, we had a problem with dates. Every single time we had to query a date field they returned to us in a 4 digit year and a 3 digit day of year (YYYYDDD). Fortunately with Alteryx there is an easy formula to take care of that. The problem was that I had to recreate that formula every single time I had a date field in a data set. Just think for a second, about how much repetitive work that can be on any one query. I know that typically I would have to do this four to five times per query return. The whole affair became exceedingly annoying. Luckily, I knew a guy named Tony Moses. I said, “Tony, what can we do here? Can I use one of those fancy ‘macros’ to solve this problem I have?” Luckily the response was something to the effect of, “Treyson, this is a perfect scenario to use a macro.” And thus my first use case was born!
Now, we won’t go into the details of what we did inside the macro, but the idea was that I could drop my new tool anywhere in the workflow and I could select a field I wanted to convert, and they just converted right over to a date without creating a single formula. This is a pretty standard example of a tool that does one thing, one time. The complexity of the macro is judged both by the need of the tool and the user’s imagination! If you are interested in diving into the creation of macros, here is a list of weekly challenges that can be solved using standard macros.
Week 39: Trouble Shooting a Broken Macro (Here you will see an existing macro in action)
Week 66: My Wine Problem (Here you will actually use the interface tools to create a tool that a customer would use)
Week 74: Build a Factorial Calculator (Who doesn’t love math problems? Another easy interface macro example)
There are two things I really like to talk about. The first is my six-month-old son, Max and the second is how to make your life better with Alteryx. I hope this article was helpful, and if you have any questions about creating standard macros, or pressing questions on any other type, please leave a comment below or reach out to me on Twitter or LinkedIn.
