My Journey to Alteryx Ace-hood

I am an Alteryx Ace!

I was so thrilled when I found out I was an Alteryx ACE, they are amazing group of people who are held in such high esteem.  I’m not sure if I will ever get use to the fact, that I am now counted among them.

My initial thought was that I thought after finding out I was an Alteryx Ace is the blog I share “Intersections and Overlap” with Samantha Hughes and Tim Rains might need to change the subtitle of “The Geospatial Trinity – Two Geeks and a Hipster” to possibly “The Geospatial Trinity – Two Alteryx Aces and a Doctor” given the successful year we all had.

Where did my Alteryx Journey begin and how I got here?

My Alteryx journey actually began by accident and coincidence, I became one of the first 4 people within Sainsbury’s to use Alteryx. However I was not initially intended to be one of those people, at the time we had Alteryx on test desktops computers with a higher version of windows and higher specs compared to our standard machines, within the organisation. The machine I eventually commandeered was meant for my line manager at the time, however they left the business when Alteryx landed at Sainsbury’s. As the machine was a higher spec machine I started use it for my GIS analysis using MapInfo at the time. My colleagues Tim and Sam both encouraged me to use Alteryx and I was instantly impressed on how fast and easy it was to use. I very quickly became a power user of Alteryx automating various aspects of my role and helping in the strategy of our team of removing SAS dependency. I have Iain Sterland to thank our Head of Department who let me use the machine and also letting me keep Alteryx.

After decommission of SAS within our department we moved onto removing MapInfo and replacing with Esri ArcGIS PRO plus also redeveloping bespoke tools in Alteryx.  During this time we were also developing other use cases and providing self-service analytics, to organisation for all things Spatial. A key aspect of my role in Sainsbury’s was to answer any question regarding Spatial, Alteryx greatly helped in answering these questions. This was great training and helped me build my expertise in Alteryx as I never knew what possibly question I could face.

After attending 2017 Inspire Europe I decided that I would take up Alteryx weekly challenges, to improve my skillset and get into the top 10 weekly challenges. I eventually succeeded and became a top challenger in February 2018.  I then went onto becoming certified passing my core certification in May 2018. This awoke a competitive side in my team with all of us trying to become the first Advanced certified. I am happy to say that I was first person to pass both certifications, but it was an intense competition between us all.  This set the trend for other members in the team and various Alteryx users in the department becoming certified.

In the summer of 2018 I convinced both my colleagues Sam and Tim that we should do a talk at Inspire as I felt we had a story to tell. The talk was well received and we got some amazing feedback and it was great presenting with two colleagues who I worked so closely with for over 5 years. To the delight of both Tim and Sam I was extremely nervous before doing the talk rather than my usual confident self.

Inspire Europe 2018 (Me, Samantha Hughes, Tim Rains)

Like all good things must end I had taken the decision to leave the team and Sainsbury’s and join Keyrus as a Consultant and had handed in my notice, a few weeks prior to the Inspire Europe conference in 2018. My nervousness before delivering the talk was due to wanting to deliver a good presentation with the team, I was leaving and wanting to impress the colleagues in my new company also sat in the audience, watching me present.

My first one to one with my line manager Jamie Laird after joining Keyrus, was about potentially aiming towards becoming an Alteryx Ace. We had an honest conversation of what activities we thought would be required in order to achieve this. One of things we spoke about was blogging and when Sam and Tim asked me in early 2019 if I would interested in blogging with them, I immediately said yes.  The idea session we had for the name of the blog was so funny I was on a train at the time and I was laughing so much at some of the suggestions. My first ever blog I posted was republished onto the Alteryx community, the blog has continued with a number of successful posts and also now features as one of the blog on Alteryx’s website.

After being an Alteryx user for 5 years I decided that I would give back to the Alteryx community and began starting to answering questions on the community page. I quickly became one of the top solutions authors and thoroughly enjoyed sharing my expertise and helping others.

This led today the 17th of October where Libby announced me as Alteryx Ace in London where I was able to celebrate this honor.

Thank You

I would like to thank so many people in supporting me to becoming an Alteryx Ace. Iain Sterland, Samantha Hughes and Tim Rains had a massive part to play in this, with the years of training and mentorship. Jamie Laird for his guidance, supporting me and being the person to actually steering me towards aiming to becoming an Alteryx Ace. Thank you to everyone who nominated me this is amazing.

I look forward to sharing my Alteryx knowledge as an Alteryx Ace. You can also find me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Alteryx: Excel Templates with Blob Tools

Have you ever needed to output your results from Alteryx into an Excel report and does that report require formatting well this blog is for you. This was a technique I picked up in my previous role and spoke about at Inspire Europe 2018 which you can watch the presentation on this link

High Level Approach

The key to this technique is to use the Blob Input and Blob Output to pick up your excel template from one location and then save it to a new location which your Alteryx workflow is also outputting the data to. The only sticking point is you need to ensure your blob tools complete before the output data tool to ensure your template is saved ready for the data being outputted by Alteryx.

Alteryx Excel Template

In the example below is an Alteryx workflow that generates a report for top 20 car makes sales for the months of June, July and August for a mock car garage. The tool container in Blue is not important and just merely represents the function an Alteryx user would perform to get the data ready to produce an excel report. The focus on this blog will be on the Green tool container which is where I move my template from one location to be used to produce a formatted excel report. 

Alteryx Workflow Excel Template

The approach to use Blob tools to move an excel template from one location to an output location is not very complex. I use a Sample Tool to limit my record to 1 as I want to only move my template once. I have also connected my Sample Tool directly after the Input Data Tool as to initialise this part of the workflow so that my template transfer across before the rest of the workflow completes.

Blob Tools Excel Template Overview

The Formula Tool creates two file-paths fields, Input which is where my excel template is located, then Output is where my template should be saved to. The Select tool in this scenario is removing unwanted fields however is actually redundant as the Blob Tools would ignore any extra fields in this situation.

Formula Tool Overview

Configuring the Blob Input is relatively simple choose a dropdown option for “Modify File Name Using Optional Input” which I have selected “Replacing Entire Path With Field” and used my field Input. This will pick up my template from the file-path location and convert it into a Blob field type.

Blob Input Overview

The Blob Output tool is a similar setup to the Blob Input Tool whereby I select “Replacing Entire Path with field” for “Modify File Name By” and I select Output for the field. This will then move my template to the output location.

Blob Output Overview

By using the Blob Tools I essentially pull in my excel template convert it to a Blob which doesn’t affect the formatting or any formulas within the excel files and then outputs to the location specified, ready for the data to be outputted by the rest of the workflow. The Output Data Tool needs to write to the same location which you are saving your template to. 

Output Excel Report

In my example I write to back sheet via the Output Data Tools. The front sheet has been formatted and contains conditional formatting and also has a graph based of the data inputted into this template. By using this technique of using the Blob tools I can create a template to match a company’s colour branding, add in their own logos, customise the report for the end user while being able to automate populating the data from Alteryx and ensure that my template is not corrupted.

Excel Formatted Report

Hope you find this blog helpful. Please leave a comment especially if I missed out a tool or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Alteryx Community: 100 Accepted Solutions in 3 months

I’ve just completed a feat I thought impossible 3 months ago I just got my 100th accepted solution yesterday. I thought I share my motivation, what I’ve learnt and my insights from answering questions on the Alteryx community.

Alteryx Community: Solution Badges

Motivation

A question I was asked recently, what motivated me to take up answering community solutions? The simplest answer is I wanted to give back to the Alteryx community, of which I previously and continue to benefit from. Like many Alteryx users whenever I have faced a problem that I couldn’t quickly solve, I have searched the community to see if similar problem existed and used and adapted a solution if present to my own situation. Being an Alteryx user of over 5 years I can remember clearly googling a problem in my early days of using Alteryx and finding no results, as the community had not been created then. The community is an amazing resource and is only so and will only continue to be, by the contributions of Alteryx users. Since I have benefited from the community with other fellow Alteryx users, volunteering their time on top of their day jobs I felt it was time I did so too and return the favour.

In my professional life the most consistent feedback I have received is my willingness to help others. I always have ample time to help anyone who is prepared to stand up and say they need help with a problem. I know for some people this a difficult task to ask for help, hence why I have time for anyone who does ask for assistance. If my experience or expertise can be a benefit to others and help them with their problems, I’m happy to share my expertise. I able to recognise that I have only gotten to where I am today, from having great mentors such as Tim Rains, Samantha Hughes and Iain Sterland who have freely shared their time and expertise with me. Therefore it’s only right to share my expertise as well to whomever may benefit from it.

Alteryx Community: Problem Solver Badge

What I’ve learnt

I have learnt a lot from answering questions on the community the first benefit is understanding the current challenges/problems that Alteryx users face. As a consultant at Keyrus one of Alteryx partners this is important as I am one of the trainers for our internal consultant population and additionally also one of the lead trainers for our Alteryx training course, we hold monthly training for new Alteryx users. This means I can adapt my training to keep it current and more relevant and focus on key areas I’ve identified from answering questions on the community.

The second benefit which I feel that is not spoken about often, is learning from when your reply to a question is not accepted as a solution. In some circumstances you can have a number of Alteryx users answering a question. As Alteryx is such a great tool and there are always usually more than one way to answer a question it’s possible for someone else to come up with a slicker solution. I love slick solutions if someone else comes with an approach using less tools and is simpler which gets accepted as solution. I use it as a learning opportunity and try to understand their approach to the question. The results of this just means my Alteryx knowledge just increases which in turns means I can tackle more questions on the community but also take that knowledge into my day job.

Alteryx Community: Enlightened Badge

Recommendations to new/potential solution Authors  

I was also asked recently was what I would recommend to any Alteryx user who wants to start tackling Alteryx questions?  My honest answer is to just start the greatest hurdle is getting your first accepted solution. I know some Alteryx users may be concerned they don’t know enough or can’t compare against some of the Alteryx greats who also answer community questions. However my own opinion on this matter is the community will not reach its full potential unless everyone feels able to contribute. If you feel you can tackle a question I would encourage you to try, as I stated earlier if someone else comes up with a slick solution which gets accepted, then learn from that experience. Also if you do contribute it might be you who comes up with a better approach  because the question is on a topic you have faced in your own work experience, then it would be you sharing your knowledge and expertise with the community which benefits everyone.

Hope you find this blog helpful and Insightful. Please leave a comment below or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Guide to Outputting to Multiple Excel files using Alteryx

I’ve recently taken up answering Alteryx community questions and this topic has come up a few times. Is it possible to output my data to multiple excel files and different tabs within those excels files? There are a few possible ways to achieve this in Alteryx in this blog I will demonstrate what they are.

Data Input

I have mock data of sales by product for 3 locations, which I want to output into different excel files by Location and Product per tab. The reason for this is that the end stakeholder will have a file per location and the different products on individual tabs.

Data: Sales by Location per Product

Manual Approach

I could build different streams in my workflow filtering by location and product and then use an Output tool to write to different excels. However this approach is quite manually and not dynamic, imagine if my data is a monthly report and the data changes this approach would not be able to easily cope and would require me to change the workflow per month.

Filtering per Location per Product

Reporting Tools Approach

Thankfully in Alteryx there is a more dynamic solution the first way I will show you utilises the Reporting tools. What I like with this approach it only take a few tools to achieve the desired results and this approach is dynamic. If the number of locations or products increase or decrease this approach would generate the appropriate number of excel files and tab sheets within those excels.

Reporting Tools Approach

In the table tool the key thing is to group by your fields you want to create separate files and sheets for in my example I group by Product and Location.

Table Tool: Configuration

The next tool I utilise is the layout tool for the Layout Mode it’s important you select “Each Group of Records” for the Layout Mode, then change the Orientation to Vertical with Sections Breaks. Changing the Section Name is optional however I used the Product field in my example as I want the separate tabs to be named as the actual product.

Layout Tool: Configuration

The last tool in this approach is the Render tool where I choose a Specific Output file and tick on “Group Data in Separate Reports” and selecting the Location field. In the Report Data section I change the “Date Field “to layout. Based upon my configuration on the layout tool it will produce a layout per product for each location.

Render Tool: Configuration

The output of this approach is three excel files are created with a tab for each product. A benefit of this approach is you can format the table.

Results: Location1.xlsx

Output Data Tool Approach

The second approach is using the Output Data Tool functionality to “Take File/Table Name from Field”.  This approach you only need two tools a formula tool to create a file path with your fields as inputs. Like the Report Tool approach this solution is dynamic and would produce the appropriate number of files, with the correct number of sheets based upon your data.

Output Tool: Overview

As stated in the formula tool I create a filepath field for the Output Tool and use the field Location as a file name then add the file extension. The Product field is used to state the sheet for the excel file (“E:\Trinity\Multiple_Excels/”+[Location]+”.xlsx|”+[Product]). 

Formula Tool: Filepath

In the output tool you want to ensure Output Options (3) Overwrite Sheet (Drop) is selected. Then at the bottom section of the configuration window of the output tool you want to tick on “Take File/ Table Name from Field” then from the drop down select “Change Entire File Path”. The last step is choose the field for the “Field Containing File Name or Part of File Name” section. You also have the option of keeping the field in the output with the “Keep Field in Output” checkbox.

Output Data: Configuration

The results of this approach is similar to Reporting Tools approach in producing separate excel files with different tabs. The only major difference with this approach you are not able to format the table before it’s outputted to excel.

Results Location1.xlsx (Output Data Tool) approach

Batch Macro

The last approach is a batch macro which is personally is my least favourite approach as it requires more steps whereas the other approach utilise a handful of tools.

Batch Macro Overview

This approach is similar to both approaches where essentially you want to separate your data, by location and product. At the beginning of this blog I showed you could build a manual process a batch macro in this situation is essentially that manual process however with a looping process making this a solution dynamic.

Inside Batch Macro

The Batch Macro utilises a filter which is updated by the two Control Parameters changing the values in the filter and then updates the formula within the formula tool, which is creating a file path for the Output Data Tool. The configuration in the Output Data tool is changing the entire file path to our selected field e.g. Filepath field created in the formula tool. 

As you can see this approach is similar to the second approach and is using the functionality of Output Data Tool to change the file path. This approach involves more steps and I think conceptually is more difficult to explain to a user when compared to the two other options. 

In summary there are many ways of outputting your data in Alteryx into multiple files. I typically only show the first two methods when I am teaching a user how to answer this question.

Hope you find this blog helpful and hopefully I highlighted the different ways you can output to multiple excel files. Please leave a comment below or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

What Tools in Alteryx can do Spatial Analysis that aren’t in the default Spatial Category?

I feel this blog will be more a test of my knowledge of Alteryx than anything else. The challenge is to name the tools in Alteryx that are capable of spatial analysis that are not part of the default category of spatial tools. The added caveat is I won’t be naming any tools that you need to purchase an additionally package, such as the Address Tools or Demographic Analysis tools.

Alteryx: Default Spatial Categories of Tools

Map Input

The first tool is the Map Input tool from the In/Out category which allows the user to digitise on a map creating points, lines or polygons from a reference map.

In/Out Category: Map Input Tool

Preparation Tools

The formula tool contains over 30 different functions just for spatial analysis and can essentially replace many of the default spatial tools. The formula tool in most cases is the Swiss army knife of any Alteryx workflow. The link provided gives more detail in regards to the functions in the formula tool link.

Preparation Category: Formula Tool

As the formula tools has spatial functionality the other variants of the formula tools, Multi-Field Formula and Multi-Row Formula also share some of those functions. The Multi-Row formula is quite a powerful tool for spatial analysis when you are calculating distance between points. The Alteryx weekly challenge #26 could be solved with two tools if you utilised the Multi-Row formula.

Alteryx: Weekly Challenge #26

Summarize Tool

The Summarize tool is capable of a number of spatial processes such as Combine, Create Intersection, Create Bounding Rectangle, Create Convex Hull and Create centroid. The following link provides more detail in regards to those functions link.

Transform Category: Summarize Tool

Reporting Tools

A number of the Reporting tools give the user the ability to create a static map such as the Report Map, Map Legend Builder and Map Legend Splitter. The Report Map tool is intended to be used with other spatial inputs from an Alteryx workflow hence why it’s able to accept multiple spatial inputs, allowing for these inputs to be layered in a thematic map.

Map (Interface)

This tool display an interactive map for the user to draw or select map objects in an app or macro. I’ve previously used this tool in an app where the user could input a point which was used downstream in the app, to do catchment analysis based on drivetime or straight-line distances. 

Interface Category: Map

Dynamic Input

The dynamic Input tool has a really great feature which is Spatial Filter which uses a polygon object in the incoming data, to determine if latitude and longitude are in the database contained within the object’s bounding rectangle. In my previous role we used this features in a catchment profiler application where the end user would select a point, then I would use this point to create a tradetime area to query against our demographic data to only bring the data to sufficiently do the analysis rather than attempting to bring in demographic data for the whole country which increased the run time of the application.

Developer Category: Dynamic Input Tool

R Tool and Python Tool

The last tools in this list is the R Tool and Python Tool which both have libraries of tools to work with Spatial Data. There are entire books dedicated to spatial analysis in these code languages showcasing what the capabilities are.  As both these tools enable a user to write code scripts you have nearly endless possibilities in term on analysis.

Developer Tools: R and Python Tool

Hope you find this blog helpful. Please leave a comment especially if I missed out a tool or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Erroneous Locations: Misdelivered Products

This problem is something I covered when I spoke with my colleagues at Alteryx Inspire London 2018 and is available if you wish to listen to that talk Link. In this blog I thought I would cover in more detail the solution to resolve this problem.

Misdelivered Products

The problem sounds simple but in fact is quite an interesting and complicated problem when thought of in more detail. Products that should have been delivered to a number of stores that are in demand for those products has mistakenly been misdelivered to stores that don’t require that product. Is there a way to work out a way to collect the products from those stores that don’t require them and deliver them to the nearest store?  As the stores only stock a limited amount of these products you are only able to collect from those stores, which have those products once and deliver it to only one store which is in demand for those products.

High level solution

The way to approach this solution is to calculate the distance between all of your Demand Stores (Stores that need the product) and your Supply Stores (Stores with the product) for every possible store combinations/scenario. Once you have a list of every store combinations and distance you need to loop through a process of selecting the closest distance for one combination of stores and then removing that Demand store and Supply store from the list of all other possible combinations, as you have used stores and can’t use a store more than once for your solution.

I’m going to show how to solve this problem using Alteryx for my data I used a random sample of Geolytix Open Retail Points to get real world locations and created a pseudo Fascia called Guisseppe. To produce 100 Demand store locations and 120 Supply store locations. The data for Demand and Supply are identical and have RecordID to distinguish the stores from each other.  

Demand and Supply Store Inputs

The first step in Alteryx is to use the Create Points Tools for both datasets and then use the Append Fields tools. This creates every possible combination/scenario of Demand and Supply stores with our data.

Append Fields Tool: Append Demand to Supply Stores

The second step after the Append Fields tool is the Distance tool which can give the distance between two points. In our case would be the Centroid Demand and Centroid Supply fields and using straight line distance in miles.

Distance Tool: Distance between Points

The following steps are data preparation where I sort by distance getting the nearest distance, to furthest distance and I remove the two centroid fields. As I no longer need them and keeping them in the workflow will affect performance.

Data Preparation

The last step is to create the looping processing of selecting the closest distance for a combination of stores, e.g. Demand store 1090 and Supply store 1536 with a distance of 0.27 miles and then removing those stores from the list of other potential combinations and then repeating the process. This is done in Alteryx using an Iterative Macro which is a macro which will run through every record and then loop the records back through the workflow, repeating the entire process as many times as is specified, or until a condition is met.

Alteryx workflow Iterative Macro

Looking inside the Iterative macro the Sample tool takes 1 record and then immediately outputs the record this will always be the nearest distance due to sorting by distance before the macro. From the Sample Tool we then use a combination of Join tools to match up the Demand and Supply Store Record ID to the list of the combinations. We then only use the Right Join of both tools as this is the remaining data minus the Demand and supply store from the Sample tool. What we are left with are then remaining possible combinations of stores and it’s this data we want outputted to the L output, which for this Iterative Macro is the iteration output that we want this macro to loop through to find a Supply store for every Demand Store.

Inside Iterative Macro

In the Interface Designer for this macro it’s important we select which Macro Output is the Iteration Output and also select the maximum number of iterations and then what we want to happen, when the maximum number if iterations are hit.

Iterative Macro: Interface Design

Looking at the O output from the macro we can see 100 records have outputted. This means we have the nearest distance to a Supply store that could possibly provide the missing products for every Demand store. Also we not used a store more than once, thus avoiding the situation of going to a supply store and finding the products having already being collected, or delivering the products to a Demand store and finding they already had those products delivered.

Erroneous Locations: Workflow Overview

Hope you find this solution helpful. Please leave a comment below or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Postcode Parser: Alteryx Workflow to Alteryx HTML Macro

A common problem I have come across when geocoding data from the UK is extracting the postcode from a single string field. The issue being unable to dynamically extract the Postcode without having to split a single column into multiple columns, based on a delimiter and then creating some logic to capture the Postcode from the correct columns.  Previous solutions I’ve come up with to solve this problem, utilising such a methodology are typically one-offs tailored for a specific data set and not repeatable. However the first the Alteryx User Group (Midlands) I attended showed me an alternative solution. In this blog I going to show how to solve this problem first in an Alteryx workflow then produce a Macro so that I can reuse this solution. Then finally I am going to turn my macro into a HTML macro using the HTML SDK.

Postcodes in a Single String

The first Alteryx User Group I attended exposed me to Regex (Regular expression) which is a sequence of characters that define a search pattern. Regex is a very useful and powerful toolset particular for extracting postcodes as there are only 6 sequences for UK postcodes so you can code for each sequence and extract from a string field.

Alteryx Workflow

Below is the Alteryx workflow I developed which I will explain in more detail. The workflow doesn’t utilises many tools and essentially creates a Record ID for each row of data to be joined again later to ensure none of the original data in the workflow that is inputted is lost. The Regex tools extracts the Postcodes from the character string for all 6 possible Postcode formats, plus outputs the reminder of the original string column minus the postcodes parsed into 6 relevant fields respectively. The formula tool combines all the columns together using an If formula to merge all the columns into one Postcode column and one address field. The Select tool deselects the unwanted fields then the Join tool combines the data stream of the extracted postcodes to the data stream with the original data, this is so any data that was not parsed will be outputted in the right join to be investigated.

Overview of Alteryx Workflow

The Regex tool in the workflow is set to parse as an output method and the Regular Expression holds the sequence of characters I am looking for. A number of output fields are created for each sequence I am searching for. In fact I create six Original_Field, Address and Postcode fields as that is how many sequences I am searching for. I will admit I could write a more condensed Regular Expression however this expression in my opinion is easy to troubleshoot and explain as there are 6 possible postcode formats I am looking for. Also this Regular Expression gives me two columns an output column with the postcodes and then a column minis the postcode sequence I have parsed.

Regex Tool Setup

As stated there are six possible formats for a UK Postcode the Regex Code below shows in more detail the code that parses the Postcode.

Regex for UK Postcodes

The output of this Regex in Alteryx will create a column an Original Field an Address and Postcode for each Postcode sequence. As you can see from the output the Regex is extracting the Postcode from the Address field. AA9 9AA was extracted in Postcode1 because in the Regex the sequence of “\u\u\d\s\d\u\u” was found in the string and the same occurs for the other sequences I have searched for.

Regex Output

My approach to parse UK postcodes create six versions of every columns and some of the rows of data have null values. Therefore I utilise a formula tool with an If formulas to replace any null rows with a row that does contain data for each relevant column.

Formula Tool

The select tool removes unwanted fields as I have used the formula tool to condense the data I have parsed into three columns as seen below. The Output has an Original Field which is the data being inputted then an Address field which is the string minis the sequence parsed from regex and then finally the Postcode field, which is parsed due to matching the Regex sequences found from the input field.

Select Tool Removes Unwanted Fields

The last tool is the Join tool which allows any other columns from the data set to be joined onto the data stream of parsed postcodes. Any data that hasn’t been parsed will come out of the Right output and can be investigated as to the reason why, while anything that has parsed will come out from the Join Output.

Output of Workflow

The result of the Join output shows that I have successfully extracted the Postcodes from the string field and I have also created an address field. The Regex I utilised in this workflow could be utilised for other data sets and could be part of a dynamic and re-usable solution.

Alteryx Macro

The first way to re-use this Regex is to create a Macro in Alteryx. A macro in Alteryx is a workflow or a group of tools built into a single tool of an analytical process that you perform repeatedly. An added benefit of macros is you can give your users options for how they use the macro. The Macro can be saved to location on your Computer or network added to the Macros Tools in Alteryx and even the icon can be customised or you could use standard icons provided by Alteryx.

Macro Interface Overview

To change the original Alteryx workflow into an Alteryx Macro wasn’t difficult all I needed was a Macro Input and Two Macro Outputs and a Dropdown interface tool to allow a user to choose which field to parse.  The only change required from the Alteryx workflow was a select tool this was in order for the action to update the column name to be used in the Macro. Otherwise the workflow would error as the regex tool wouldn’t know which field to parse. However for my macro version of this solution I wanted enable the end user to choose the output they receive so have added in a radio button option which disables a tool container when selected. The options of the radio buttons affects if you receive an address column as in some cases you just may want the postcode to geocode and wish to leave the original field intact.

Macro Workflow Overview

The workflow now has two tools containers both of which are enabled, however when the end user selects one of the options of the radio buttons it subsequently disables one of the tools containers. The results of the enabled tool container are pushed through the union tool and is outputted. This setup means no matter what option is selected, results should always be passed through this macro. The tool containers are controlled by the radio buttons interface tools for example when the user selects Regex Parse: Postcode it will disable the tool container labelled “Regex Parse: Address and Postcode”. I utilise a dropdown tool to enable the end user to select what field is the address string they want to parse the postcode from. The results of these changes means now this solution is more dynamic and can be more easily applied to other datasets. 

When creating a macro Alteryx provides you with a list of default standard icons images however for my macro I have created my own custom icon, which I created using a software called Inkscape. Weekly Challenge 97 is very useful if you want to get the tool icon to use to create your own custom icon that looks similar to the other Alteryx tools as I have done for this macro.

Custom Icon Macro

The output of the weekly challenge 97 is that you create an image for every tool icon in Alteryx. I utilised the Regex icon and used Inkscape to trace the icon and then I fill in the white spaces with the same colour of the icon and then I paste an envelope icon on top then save the image as new icon for the Postcode Parser Macro.

Inkscape: Creating a Custom Icon

Html Macro

Why create a custom tool (Html Macro)? It makes sharing easier as the file format .yxi is just a zipped package with all the tool and components. When opened the tool is saved automatically and added to the tool bar and is ready to use.  The Alternative of just using a standard macro within Alteryx and sharing with other users it requires access to the same location, where the macro is saved and setting up Macro repository via user’s settings in designer.  This process can be time consuming and requires a number of steps the alternative offers a more seamless approach.

Macro User Setting

From previous experience of hosting macros on a shared network drive if your user’s connection to that network drive is interrupted they also lose access to macros. Another issue I found is the Macro tool category is you have to scroll to find on the right hand side of the tool bar and is not necessarily the most intuitive place.

Macro Tool Bar Category

There a number of useful links when creating customs tool which helped me.

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-143-Developer-Community-Anniversary-Edition/td-p/333201

https://community.alteryx.com/t5/Engine-Works-Blog/Building-an-HTML-Macro/ba-p/158691

Alteryx also provides an example of both Html and Python SDK in your version of Alteryx Designer which is useful when creating your own tool as they provide examples widget saving you starting from scratch.

Folder Structure

 The most important thing when creating a custom tool is the folder structure essentially you need to create a 3 tier folder structure. The first tier has a folder with the tool to be installed the config.xml and then an icon. Then next tier holds a folder with the supporting macro then another config.xml for the tool then an html file and icon that make up the tool. The last tier holds the macro of which the tool is based on.

Overview of Folder Structure

Config File: PostcodePaserToolConfig.xml

This xml tells the Alteryx engine what the tool is and how to use it. It essentially the back end plumbing between the macro and the tool. Here you need adjust the Engine Settings the GUI Settings and the connections.

PostcoderParserToolConfig.xml

The connection section should match the number of Inputs and Outputs as your in macro they also must have the same name in the connections as in the macro. The reason for this is so Alteryx knows what the inputs and outputs streams of the tool are. For the EngineDllEntryPoint and the EngineSetting sections it points to the Marco I’ve built so that the instructions from the tool is passed to the macro. Most importantly you can select the Category for which the tool should be included in. As I have chosen Parse it should be installed with the other Parse tools such as the Regex tool.

Building the User Interface (PostcodePaserToolGui.html)

For the user interface I utilised HTML – GUI library example as it has a vast choices of widgets. Building the interface was trickier than I originally anticipated. I had to reach out to the Alteryx customer support and the community to find a solution. The solution to building an interface similar to my macro did mean I had to make some changes to the original macro.  For the Postcode Parser Tool I needed a DropDown widget and choose field selector enabling my user to select a field from their workflow. For the radios buttons I had to utilise a radiogroup and specify my options and the values that would be passed into the workflow when a radio button was selected.  The dataName needs to match the names of the interface tools used in the Macro.

HTML Interface Part 1
HTML Interface Part 2

As the dataName needs to match the names of the interface tools and also as the radiogroup is passing a string value when selected I had to configure the macro to accommodate for this. I used a dropdown interface rather than two radio buttons with the selection of the dropdown matching the values of the RadioGroupSelector. This setup still has the same effect of the original macro when the end user makes a selection only one of the tool containers will be enabled and pass through the results.

Custom Tool Macro

Despite changing the macro from using a Radio buttons to a dropdown to control which tool container is enabled the interface shows the options as radio buttons due to selecting the radiogroup widget from GUI library.

HTML Interface Postcode Parser

Config.xml

This config file is the Metainfo displayed on the pop-up when the tool is being installed.

Config Display Installer

The MetaInfo provides important information to the user but allows you to version and author your tool.

YXI Creation

To share the New HTML macro you need the zip your tool specific folder.

ZIP File

After you zipped all the files change the format to .yxi a warning error will appear but press yes.

Changing Zip File to an .yxi File

This is the final step and now you have your installer for your custom tool.

PostcodePaserTool.yxi

Once this tool is shared the user just needs to click on it and it will automatically install on that users version of Alteryx designer and be placed into the tool category you specified.

Hope you find this solution helpful and shows how to can develop a solution into a reusable tool. Please leave a comment below or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.