Since I started the data school I've been hearing about macro's in Alteryx, and we had a look at the macro that made up the cleansing tool. I remember thinking 'what on earth is this?' It looked sooooo confusing.
This is the macro that makes the data cleansing tool:
I'm definitely not at the level of making macro's like this yet, but in this blog, I'd like to take you through some macro basics.
Macro's are used when there are series of actions that one does to a document repeatedly. If you're familiar with Alteryx, you will know that tools are dragged on to the canvas and joined in a series to change the data. In an organisation, there may be a series of actions that you always use. Instead of having to repeat the time consuming process over and over again, you can do it once, make sure it works properly, then save it as a macro so the next time you need that sequence of tools, you only have to drag the one macro tool instead.
Hopefully, I haven't confused you and you're ready to find out about the types of macros and how they work. Without further ado, let's get this show on the road.
Types of macro's
There are 4 types of macro's. In order of difficulty, they are:
- Standard macro's:
These macro's take you through a single process and each time it's run, you get exactly the same thing. It's like following a recipe to make the exact same type of cupcake over and over and over again. A basic vanilla cupcake with buttercream ice cream for example.
Here is an example of a basic macro with a before and after of the output:
This macro takes an input, removes a set number of rows and then outputs the data, with the added option of letting the user select just how many rows they want to be removed. In our example, the first 4 rows of data aren't really needed. As is shown, these were the rows where the title and other information about the document were kept; they aren't actually part of the data and so we don't need them. A basic macro is used here because we just want to remove excess rows from the top every time data is input into the workflow.
2. Batch macros:
These macro's are used when there is some sort of grouping required. Say you still wanted to make cupcakes, but you wanted different flavours and so needed to tweak the recipes slightly. A batch macro would let you make changes and have them specific to the cupcake flavours.
To use a slightly more data driven example, say you have a company that sells accessories, and your costumers have to pay tax, but the tax rate is dependent on the countries. To find out how much tax a person has to pay, you'd first need to group the customers by country and then apply that country specific tax rate to whatever the customer is buying. An example of this is below, this time I'm using states in the US.
The customers are grouped by the state abbreviations and then the tax rate which is associated with that state abbreviation is applied to the customer and the results are given in batches hence batch macro's.
3. Iterative macro's:
These macros are goal oriented. They keep repeating a sequence of events until they've hit the preset target. Say you wanted to save £100 every month, and wanted to know how many months it would take for you to save £1,300, you could use an iterative macro to do this.
In a more realistic example, say you wanted to invest £200 a month into a fund that gave you an interest rate of 2% per annum and you wanted to know how many years it would take you to reach £50,000. In terms of the math, this means that each year, we have to add £2,400 to the amount from the previous year and then multiply that by 1.02 to find out how much we'd have with the interest earned. In alteryx this will require lots of repeated formula tools. (way more than 3 which was the maximum number of repeats I was willing to try).
Here's what it looks like without the macro after 3 years. You're still far from the £50,000 target and we just have a sequence of repeated formula and filter tools:
Using an iterative macro means that we can get the repetitions done automatically till the target is reached. With an iterative macro, you need to outputs. This is different from the other macros.
The 1st output, Output O, will output the data once the target is reached (it goes out of the true section of the filter tool). This will happen once. The second output, Output I, is the iterative output which feeds back in to the tool. It's coming out of the false section of the filter tool because the investment amount has NOT reached the target of £50,000 yet therefore we want to keep putting our end of year amount back through the formula tool until our target is met.
Here is the result, it would take 18 years to reach our target. Without the iterative macro, we would have to input the exact same formula and filter tool 18 times. #wasteofenergy
4. Location Optimiser Macro
So I don't actually know anything about this macro yet but I wanted to mention that there is a fourth one. I promise to write a follow up blog once I learn about it though sooooooo.........
