One of the topics covered by DS12 this week has been macros which included learning the difference between standard, iterative and batch macros. We also briefly delved into the world of apps. I wouldn’t really consider myself the most tech-savvy person so finding out I could (and would) do all of this easily in Alteryx without having to know how to code was mind blowing.
In this blog post I’m going to talk about the general steps I take when making a standard macro and what I learnt whilst attempting Alteryx Weekly Challenge #66.
Those of you who are familiar with the Alteryx Weekly Challenges will know that the general format for these is that you have an Input and Output data set provided as a beginning and end point for the challenge. For challenge #66 we are provided with data that looks like this:
Using a macro we need to get the data to this format (the specific values will depend on box size which is specified in the second photo below:
As you may have noted, we can see in the photo above that the macro is also required to meet certain criteria with respect to error conditions which is where the test tool and subsequent conditions will come in. Before I talk about that, though, I just want to talk through the steps I took whilst approaching this problem.
The first step to creating a macro is to make the workflow and I find it helps the process to focus on one particular scenario first before making your macro suitable for general use. This is my workflow prior to turning the flow into a macro:
I have focused only on the situation where box size=6 and produced output data that matches that given in the challenge for this box size. This tells me the maths works and I can move onto the next step which is to turn my workflow into a macro that can manage different box size inputs. I did this using the macro input and output tool and made the calculated field box size dynamic using the interface tools: Numeric Up Down and Action tool. The macro workflow can be seen below.
The macro I have produced so far can handle the user inputting box sizes and the resulting outputs match the results given for the different box sizes, however, I still need to fulfil the last part of the brief and include test conditions. To save you scrolling back up to the original challenge picture; here is a reminder of the requirements the macro must meet.
The macro must check for, or prevent, three specific error conditions:
- A null value for either the from or to value on an order range
- Non-numeric values in the from and to values
- Situations where the from ID is greater than the to ID
I am going to use the developer test tool to implement test conditions that, if met, will throw up an error. I have never had to use a test tool before this challenge and so I wasn’t really familiar with the tool’s function or that it was the key to fulfilling the above criteria (thanks for your help Tim Manning!) so it was pretty cool getting an opportunity to use one.
I’ve attached this work flow to show the placement of the tool. Having the attached to the input source enables the test tool to pick up null values as I found that placement at the end of the flow meant that the first error condition was not tested properly as any null values are lost at the generate rows tool.
After attaching the test tool it needs to be configured. This can be done by clicking on the tool and clicking ‘add’ in the test – configuration box (the box that will pop up has the same layout as the edit test box except is labelled add test and only the input connection and test type box will be pre-populated. The input connection will have a number corresponding to a line into the test tool and test type box will contain ‘Record Count Matches Specific Value’. When adding test conditions you will need to add a test name (bearing in mind that the test name will pop up when an error is identified), select the tnput connection if more than one connection feeds into the test tool, select the test type using the drop down (in this case I want to test all records) and the test value. I have set up my boolean to be ‘False’ if an error condition is present as the test type is pre-set so that ‘True’ is a pass. The test names and test values are numbered to correspond with the numbering of the error conditions above.
Now that I have set up my test conditions I want to check that it works. To do this I can feed the macro some ‘bad’ data and see if it identifies the error correctly. I can do this simply by modifying the Input data set provided for this challenge. In this case I’m enforcing the first error condition and we can see that the error is recorded in the results-workflow-messages section. I tried enforcing the other two error conditions and the errors were picked up by the macro. I’ve built my first macro with test conditions!
