INTRODUCTION

For this blog, I wanted to focus on a topic that I am perhaps not so confident with: Macros and Analytic Apps. I will give an example of each to further my knowledge and the readers.

MACROS

The premise behind macros is to save time. By this, I mean that by converting a few Alteryx tools into one tool that can be used over and over, you can spend more time being productive. A few of my favourite tools in Alteryx are macros, for example, the cleansing tool, and many of the predictive tools on Alteryx.

There are 3 different types of macros:

  • Standard
  • Batch
  • Iterative

Standard macros are designed to package a process that can be inserted into a workflow

Batch macros run many times in a workflow, creating an output after each run. The macro runs once for each record or selected group of records in data. The macro requires a Control Parameter tool as an input.

Iterative macros  run in workflows several times until a condition is met.

Macros are saved at YXMC files, YX for Alteryx, MC for Macro

Standard Macro

To begin I want to use an example most are familiar with, so I will explain the cleanse tool in Alteryx. Below is the workflow for that macro. Immediately I can see two things that need to be done to create this macro: The Macro input and output tool, which can be created by way of inserting a normal input and browse and converting them by right clicking and selecting the option.

The wireless select at the top left is having using the input from the fields the user selects, say for example, “Name” “Year”. Then the user selects what they would like to do with these fields and how to clean them. To the right we move onto modifying the case, as can be seen the options that the user can select is

Upper Case
Lower Case
Title Case

The formula used to get this to work is:

IF [#1]==’True’ THEN
IF [#2]==’upper’ THEN
‘IF [_CurrentFieldName_] IN(‘ + [#3] + ‘) THEN
UpperCase([_CurrentField_])
ELSE
[_CurrentField_]
ENDIF’
ELSEIF [#2] == ‘lower’ THEN
‘IF [_CurrentFieldName_] IN(‘ + [#3] + ‘) THEN
LowerCase([_CurrentField_])
ELSE
[_CurrentField_]
ENDIF’
ELSE
‘IF [_CurrentFieldName_] IN(‘ + [#3] + ‘) THEN
TitleCase([_CurrentField_])
ELSE
[_CurrentField_]
ENDIF’
ENDIF
ELSE [Destination] ENDIF

I won’t go through all the tools like this, as I just wanted to explain the basic concept behind the building of a macro.

BATCH MACRO

Good practice for making and understanding a batch macro can be found here

This is an Alteryx challenge (Number 17) essentially a bank wants to know their retention rate over a 24 month period. We must calculate the retention rate for May, June, July, and August. I would highly encourage you to download the workflow and follow along.

The above picture is the workflow for solving this problem. To begin with, the inputs for the dates, like open and closing dates must be converted to the correct field, this is done using the date parse tool.  The select is used to get rid of the original fields which won’t be used. Next, the batch macro is used, below can be seen the workflow for it. The start of month text input contains the four months we will be honing in on. So below the input that flows into the target anchor of the append tool is (naturally) identical to the modified input that was made in the above image. The control parameter (Gear looking tool, with a recycle sign inside) is updated using the action for which each row (each closing date) is appended to the modified input. The rest is just filtering out the null values because they are of no use. The formula tool is used to determine whether the date falls within a specific month. The count of closed accounts are then divided by the count of opened accounts to get a retention rate for each month. This is done over and over until the months that we are looking at (4 months) runs out.

ITERATIVE MACRO

To go through an iterative macro I would like to use another Alteryx challenge, I will be using number 14.

This solution was developed by Nicole Johnson, as I couldn’t find many instances of Alteryx challenges where an iterative macro could be used, mainly batch macros. You can solve this challenge without a macro, but thankfully Nicole did it with a macro so I could talk about it!

From reading the description of the task, we need a macro that will distribute items from the warehouse to each store, filling the available stock in order of the store priority. First the stores and required stock are joined using the “store” field. With that done the warehouse and the joined product go into the macro and give a result. The join and filter tool at the end are only their to check the answer.

So to get into the macro:

We see that there are two input, one for the Stores and one for the Warehouse, they are being joined on item. The step after the join is to filter by level of priority, so the store with priority 1 will be first to get the required item (if it is available) and so on. Next there is a formula tool with the formulas

  • IF [Required]<[Count] THEN [Required] ELSE [Count] ENDIF
  • IF [Assigned]>0 THEN [Count]-[Assigned] ELSE 0 ENDIF

These ensure that the difference between assigned and required becomes 0 over every iteration. The specification for how many times the macro should run can be found under the interface design. The output is going to keep running until 100 iterations have been done. If you decrease this to 50 the macro will still work. But if you try 25 then it stops working because not enough iterations have been run to make the workflow achieve its desired results.

The result may have items not allocated to some stores, but this is correct as some stores have a higher priority.

ANALYTIC APPS (WIZARDS)

Analytic Apps are saved as YXWZ, YX for Alteryx, WZ for Wizard (Analytic Apps used to be called wizard apps, this is further shown by the magic wand that you click to configure the workflow)

Essentially analytic apps are Alteryx workflows that allow user interaction. Analytic apps can be uploaded to the Alteryx gallery too. As an optional feature, the app can have a report attached to it, so that a pdf or report is generated that the user can play with. One could even attach a tableau output so that visualisations are created for the user as well.

I made an analytic app where the user can select a song and an artist, by entering the text themselves. If the values are ok according to https://www.azlyrics.com/ then my app returns the words of the song (in multiple rows), the length and count of how many times they appear. Below is the workflow with all my steps. I use 3 text inputs, two of which the value changes (artist and song) the other is the URL for the website. The rest of the steps involve cleaning, downloading and parsing the data until it can be in the correct format to be aggregated. The next step after you have this would be to put it into tableau and do some analysis on specific bands and songs. For example, now I know that Hozier’s hit song “Take Me To Church” only had church said 7 times. Perhaps not very useful, but the concept of how analytic apps should be there.

Below can be seen the interface the user can interact with:

I hope I’ve been able to give some examples of macros and analytical apps to help those who need it. If there is a follow up to this blog it will be about the reporting tools.