Batches Be Solvin’

[Treyson]

Thank you for joining us here for the final lap of this short series on the difference between macro types and when to use them. Thus far, we have discussed terminology within Alteryx that we need in order to produce our very own macros. If you haven’t read up on standard or iterative macros, I suggest you take a look at the previous articles to see where we are coming from on some of these terms.

When discussing “Batch Macros,” there are two main functions in which I, and most others, utilize them.

• Batch Macros allow you to push a single set of data against a variety of parameters, one a time, to see separate results.
• You can utilize the “Control Parameter” to change the functionality of your batch macro based on the use of certain tools.

But first, let’s define some things:

Batch: a quantity or consignment of goods produced at one time. To translate this, at this point we sort of understand that we will be throwing data at a tool we have created. We are, instead of applying one set of logic to a full data set, dividing your data up into subsets and applying logic to each.

Control Parameter: As soon as you drop this on the canvas, the workbook changes itself to a “Batch Macro.” It is the single most important piece of this macro type. The Control Parameter allows you to pass in a list of parameters that will control actions of your macro.

Sending Data Through Against Different Parameters

The first method that we are going to discuss is using batch macros to push your data against a series of parameters. If we return to our NASCAR analogy, we remember that we have already looked at a car going through a single lap (standard macro) and a car going through the same lap over and over again (iterative macros). So, for this analogy, we are going to think again about a multi-lap race, where you go through a single lap and then on the second lap there is a little bit of water on the track. It’s the same process on the second lap (batch), but there is a little bit of a difference from the first go-around because we have added a new variable/parameter. You can set up your macro to run the situation against any number of parameters and all outcomes will be provided in the output stacked (union) on top of each other. Let’s have a closer look.

For our build example, we are going to look at one of my favorite topics.

Mortgage Payments Are Basically Highway Robbery

We want to build out a tool that will allow us to put through a few examples of how much we are paying to a bank in order for us to purchase a home in the United States. For this example, we’ll look at the three basics:

1. Principal: How much the house cost
2. APR: Annual Percentage Rate. How much you are paying the man in order to live the American Dream
3. Loan Age: How many years will it take you to get out of this debt?

We start by adding these variables to a text input tool:

For most of us we then realize that we have no idea how to actually calculate a mortgage, so we check the internet and find this:

Source: NerdWallet.com

We look at that calculation and know it’s a piece of cake, so we add a few more tools and have a nice little workflow going that provides all of the information we are looking for. I assume that you will be able to get here on your own, but just in case, here is a screenshot of a mostly finished product. This example doesn’t have any down payment calculations or PMI baked in.

Okay so now that we are all riled up at the idea of almost paying double for our home when all is said and done, let’s figure out how to make this a tool in which we can pass multiple scenarios through in order figure out our best possible deal. We are first going to drop in a Macro Output and a few control parameters.

In the picture above we have added three control parameters because we know that there are three potential field values that we are going to be passing into our macro. If you look at the configuration panel on the left, you notice that I am having the action tool replace the value in our text input with whatever parameter value that we will be passing in. Save this in your macro folder and open up a new workflow!

The first thing we are going to do is drop another text input tool and create a few fun scenarios. If you aren’t clever on the fly and want to borrow some, here are a few quick options.

Principal	APR	Loan Age	Comment
300000	4.035	30	Current APR, Realistic Payments
150000	3.721	15	Maybe we don't need as much house and can pay down early
1000000	4.035	30	Checking out that baller lifestyle
300000	2.1	30	Your dad works at the Fed and can get you a killer rate

The next thing we are going to do is drop our macro onto the canvas and connect it to our text input tool. The one big new thing that we are going to see is that on your macro there is an upside-down question mark on your input. This is going to be your “Control Parameter” input. Regardless of how many control parameter tools you have in your macro, there will only be one of these types of input. You will also notice that when you select your macro, the control parameter will have added a few things to your “Questions” area of your configuration panel. When you select a series of fields, the batch macro will push the parameter on each record against the workflow.

Once you have filled out your questions and lined your fields up to the corresponding control parameters, you can run your workflow. Here are my results below:

We can see that our workflow produced four records all with different results. This is exactly how we have built this workflow to work. Each record corresponds to the different parameter records that we sent against the workflow. Now you can see how much you are paying in interest when you decide to buy a home!

Now, there are a few things that this example does not touch on. You are able to push data through batch macros in the traditional macro manner. Each one of your parameters is going to be run against whatever your dataset looks like. So if we had eight records that we were pushing against our four different parameter records, our results would be 32 records with each record showing results for each of the four parameters.

Changing the Functionality of Your Macro

The other bit that we mentioned above is the ability to use the control parameter to get your workflow to act in certain manners based on what you are seeing before the macro is utilized. The easiest way to begin this part of the conversation is to look at the coolest tools that no one uses because no one knows how they work. That’s right we are talking about the detour tools! Traditionally, to figure out how these tools work, you need to climb to the top of a mountain and find the wise old man. Tell him that you are seeking Alteryx enlightenment and he will show you the way.

But now it’s kind of like this:

(I spent more time on this than I would like to admit)

It’s pretty easy to wrap your head around the concept of these tools. There are two parts to this tool concept: ‘Detour’ and ‘Detour End.’ ‘Detour’ sends your workflow in one of two directions and ‘Detour End’ receives the end of whatever paths you have between the two. Note that every output of the ‘Detour’ MUST go back to the ‘Detour End.’

Pretty straightforward concept there. The value that comes from the use of these tools is that sometimes, you need to process data in a different manner depending on what your data looks like at a certain point. If you try to take your data in one of two different directions without the use of these tools, Alteryx will still run both forks and can cause failures on the other end of your process. For example, if path 1 needs to be structured in one manner, but path 2 needs to be structured in a completely different fashion, the Union tool and fields are going to be all sorts of screwed up on the other side.

So for this example, let’s shift into the sales space. We are going to use some sample sales data from a certain viz tool that most of us probably use all the time. Let’s pretend that we want to group our data based on the country that the sales are coming from and create a column for each of our sales categories using the ‘Cross Tab’ tool and then do some simple calculation on one of the fields. You throw it together in a few minutes and are a hero. Your boss is super impressed with your abilities and you get a raise.

A week later your boss comes back and says, okay well we like this calculation tool, but we need to do a completely different calculation if the data is coming from a different part of the world. In a simple example like this, we can definitely solve this problem by doing an IF statement in your formula tool, but for the purpose of the demo, pretend that is not an option. We now have to start thinking about sending your workflow in different directions based on newly defined criteria. You are a savvy Alteryx user and you think ‘Self, this is no problem, the filter tool does exactly this thing!’ So you make a slight adjustment to your workflow that forks everything in one of two directions and notice after a single run, that you are getting a problem.

The ‘Cross Tab’ tool is super powerful. I bring things out of my SQL environment all the time to avoid writing pivots in my SQL code. However, this tool can become difficult to use in situations like this, where because we are no longer passing data through the tool, we aren’t creating columns, and everything downstream will break.

*Note there is also a little trick around this problem involving the ‘Text Input’ tool and a union, but I can’t talk about these new awesome tools if we hack our way around things.

You start to panic a little because you don’t want your boss to be disappointed. Luckily, you remember that you read this blog and there are some neat tools called ‘Detour.’ You go over to the developer tools and drop the detour tools into the canvas and everything works as planned.

Okay so now this avoids our problem, but we know that with some logic we are going to have to get the detour tool to send our workflow in the other direction. Let’s head over to the configuration pane!

We run it again.

Neat! So all we have to do is click that check box and it does exactly what it was advertised to do. However, when you start thinking through this, it quickly becomes apparent that in the current state, you are only going to be able to run this manually because you have to check and uncheck that box. Here is where we finally get to talk about the batch macro application for this use case.

If the box is checked on your tool and you look at the XML behind the tool, you will see that there really is not a lot happening here.

The only real configuration that exists in this tool is that check box.

Unlike a traditional macro input, the control parameter can pass things into your macro that are directly fed into an action tool. How this applies to our current use case is that we want to be able to change this true/false value in our XML string based on some logic that we are going to put together outside of our macro.

So here is what our workflow will look like:

What you can see is that we are creating a formula that just returns a true or false value. In our sample we only have a single country in the entire data set. If you recall the first half of this article, we discussed that if you send multiple records into a control parameter, it will run the workflow back through for each record applying whatever parameter is in each record to each run. So for this example we are using the summary tool to get a single record that we are applying this logical formula to. That formula tool then connects to the control parameter input and selected under the questions tab.

We then run the workflow and see results. (I went back into the macro and created a flag so we can see when there is a difference of which path was run.)

The workflow passed a ‘False’ value into the control parameter, which means that the check box within the ‘Detour’ tool’s configuration was left unchecked (see back to our XML pictures) and thus took the left output.

For the sake of the demo I also want to show how it runs otherwise, so I adjusted the values within the Country field.

And ran it.

Because the value in the country field (on the control parameter side, I did not change the original data, which is why the output still shows ‘United States’) no longer shows ‘United States’ we are passing through a ‘True’ value, which then adjusts the ‘Detour’ tool’s configuration to ‘selected’ and thus detours our workflow to the right. This can be seen in the Path field.

There are a few other practical applications in which you can use a control parameter to adjust the XML of a workflow within a macro. The other really popular one is enabling and disabling the ‘Tool Container’ on the canvas.

So that’s it! Our quick and dirty recap of this article series is this:

• Macros are great
• Standard Macros can make our lives easier by reproducing something that we are doing over and over again in a workflow, by creating a single tool that we just drop onto a new canvas
• Iterative Macros allow us to run our processes on a loop, which is otherwise unavailable in an Alteryx workflow
• Batch Macros help us dynamically change our workflows by passing new parameters based on the data we have

Go forth and produce great results! There are lots of ways to use these (including nesting macros, and many have covered this topic, ie: fellow ACE, Jesse Clark’s Inspire session). As with most things in Alteryx you are truly only limited by your imagination, so get creative and try new things. Reach out to the community for help and #neverstoplearning.