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
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.
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
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.
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.
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.
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
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.
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.
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 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.
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
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Output Data Tool
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.
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]).
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.
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.
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.
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.
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.
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.
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
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.
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.
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
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.
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.
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.
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
R Tool and Python
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
There a number of useful links when creating customs tool
which helped me.
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.
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.
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.
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
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.
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.
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
This config file is the Metainfo displayed on the pop-up
when the tool is being installed.
The MetaInfo provides important information to the user but
allows you to version and author your tool.
To share the New HTML macro you need the zip your tool specific folder.
After you zipped all the files change the format to .yxi a
warning error will appear but press yes.
This is the final step and now you have your installer for your custom tool.
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.