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.
Words cannot express how excited I am to be part of the group which leads the way with Alteryx, I have looked up to users such as Chris Love, Marquee Crew, Joe Lipski and James Dunkerley for so many years and this year that is me!
So let me take you on my journey on how I got here…
I have been a subject matter expert (SME) on Alteryx within Sainsbury’s for the last 5 years at least, I have been a point of contact and enabled many projects to go ahead smoothly thanks to the use of the analytical power of Alteryx and Alteryx Server (both gallery and scheduler).
In 2016, Alteryx approached me and asked me to share my journey of Alteryx on the stage at INSPIRE. This was my first time public speaking so this was absolutely terrifying but the people at Alteryx have been the best coaches in this and supported me every step of the way. (Thank you)
What I shared was how we used Alteryx to remove the dependency of SAS within our Property teams. This enabled us to have a more consistent, reliable infrastructure in place which we could trust and support to provide our weekly sales information. I remember my first interaction in person with Marquee Crew when I answered that “…the community was the best place to go for all questions on Alteryx” during my talk and he loved that. We then became acquainted in the solution centre, where he showed me Alteryx has Easter Eggs, my day was made! This was epic information. I believe this conference was the first time I met Alteryx Ned too. Honestly, I am such a geek, I still have the slapband he gave me.
So back to Sainsbury’s, we didn’t stop at just decommissioning SAS within our department, our next challenge was to be able to replace MapInfo (a Geographical Information System) with ESRI’s ArcGIS Pro. Upon approach this wasn’t an easy task, we had a number of bespoke tools developed for MapInfo that we depend on all tied into the product. So over the time we re-developed our tools into Alteryx or Alteryx applications on the gallery to enable us to decouple them from MapInfo. Upon reflection, we actually accomplished this rather quickly, it was more about the discovery and ensuring we were delivering the tool to the needs of the user that took up the time. Alteryx made it easy and enabled us to have a middle ground in which we could develop tools to move in a new direction.
In 2017, I attended INSPIRE again in London to take part of the Women in Analytics Panel Luncheon. This was a fantastic opportunity where I met some amazing people. Working closely with Libby really showed me how passionate she is about her users and the community. Sean Adams, this was when we first came to know one another and we had some cracking laughs as well as Amy Holland (who always has excellent swag at the conference to share, mainly slap bands, you know they rock! ;))
During July of 2017, I started the user group at Northampton / Coventry to give space and share with others how important it is to help one another and develop our skills in Alteryx.
The start of 2018, I decided to go for my certification, this was spurred on Joe Serpis with getting his first. It awoke a little competitive side of me, so we all vowed in our team to get the CORE and ADVANCED certification. This set the bar for all future members of the team. Catherine can vouch for this she instantly wanted to join our amazing level and blew me away when she passed CORE within 20 days and ADVANCED within 2 months. I will never forget her dedication and passion to do this.
Later in 2017 I wrote a 4 module course for Alteryx Designer and started teaching the existing Alteryx users within Sainsbury’s so that they could be confident to take their CORE exam as part of their development. The modules are called;
1. In & Out, Shake it all about 2. Join in and Transform 3. Spatial 4. Download and Conquer
Once they completed the Alteryx course they became Alteryx Angels within the business. It’s important to help users understand their journey of their development and give them key aspirations to work towards. I found this really helpful when up-skilling the team.
INSPIRE for 2018, I teamed up with my colleagues Tim Rains and Joe Serpis to deliver an amazing talk all about spatial and how we “Alter everything with Alteryx”. It was very well received and the feedback was amazing. Standing and sharing with the Alteryx community is now something that I enjoy doing, because let’s face it, we rock and have the best times.
Within Strategy and Planning at Sainsbury’s we have a mix of people and skills when it comes to understanding data. Without understanding data and the fundamentals of it, it’s not really possible to teach Alteryx without stopping to explain another concept. So over the Christmas of 2018 I wrote a new course called Data Principles. This enabled me to teach a foundation of concepts and knowledge of data, its type, and structure and how important quality and ethics are before users progressed to Alteryx course. Within Data Principles, I also introduced geospatial concepts as well as webscraping concepts so all the users have a better grounding for what was to come.
Now in 2019 we have 83% of people in Strategy and Planning using Alteryx every day and we started off with 11%, an achievement I am exceptionally proud of. Especially when a colleague of mine John Smart tells me that he completed something in 15 mins in Alteryx when previously it used to take hours in Excel. (#lifegoals)
Leading to today 13th June 2019, I am announced by Libby as an Alteryx ACE in Nashville Tennessee, I am only gutted I cannot be there to be part of the amazing atmosphere that I know is happening right now. Thankfully I watched it live on Twitter.
Have a blast team, thank you for this amazing opportunity and I can’t wait to get involved even more. More so I cannot wait to meet my cohort in person, Kenda, Nick, Fiona, Yug and Thales – we are going to ROCK.
So of course, I had to celebrate with CAKE, as you do.
I would like to thank so many people for their support in getting me here. Tuvy, Lauren, and everyone who supported within Alteryx. Tim Rains, Catherine Duffy, Iain Sterland and everyone at Sainsbury’s who supported and nominate me. Thank you all this is amazing.
So you are a wizard at creating points and doing your spatial analysis and saving your dataset to SQL. However have you tried to use T-SQL to create points within your databases? There are a number of reasons why this may be useful. One being time savings, two that your server may be virtual and no longer physical, this certainly saved us some time publishing data in that type of environment.
For this example I am going to download Geolytix Open Retail Points. Its a great dataset for playing with and I recommend you have a view.
Preview of the dataset:
So I am going to step you through a few points to achieve the following:
Bring in the data and view the columns.
Understand that your points will be plotted using a specific projection. If you don’t get that right, you will see that points are not over the buildings, or perhaps are in the sea. (Usually a big giveaway!) The projection in this case is WGS1984 (SRID: 4326), however if you wanted to play with British National Grid (SRID: 27700), you can do as the co-ordinates for both are present in the dataset. You will need this later.
Simply add a Record ID tool. (Nice and easy)
Output your data to SQL using the output tool. Add the output tool but in the POST Create SQL Statement box, add the following.
5. Copying in the following code, ensuring that your Record ID is called “RecordID” and your spatial object / geography columns are in fact called “Lat_wgs” and “Long_wgs” and your table name is going to be “dbo.Geolytix_Retail_Points”. If this isn’t the case, make sure you amend before you hit run.
–Set Record ID to be not null in preparation for making it a Primary Key ALTER TABLE dbo.Geolytix_Retail_Points ALTER COLUMN [RecordID] int not null;
–Create a Primary Key on RecordID ALTER TABLE dbo.Geolytix_Retail_Points ADD CONSTRAINT [PK_Geolytix_Retail_Points] PRIMARY KEY CLUSTERED ([RecordID] ASC);
–Add a Geography Column for the Spatial Data ALTER TABLE dbo.Geolytix_Retail_Points ADD [SpatialObj] Geography;
–Update the Spatial Column with Lat / Long and using the correct SRID 4326 = WGS 1984 UPDATE dbo.Geolytix_Retail_Points SET SpatialObj = geography::Point([Lat_wgs], [Long_wgs], 4326);
Once you are ready hit run and you should get success with results looking like this:
So … CRUNCH TIME >>>> Results
Alteryx creating the points, using SQL Server to do the job with post SQL code = 8.9 Seconds!
Alteryx creating the points, using “Create Points” and writing it to the SQL Server = 2:18 Minutes!
As you can see this is a noticeable difference, and its all infrastructure dependent so your results will be different too. Inserting around 14,000 points into a SQL database in less than 10 seconds spatially enabled is perfect. You could always expand this to spatially index your data too.
The best part of the Alteryx platform is its totally versatile for your needs and can meet your needs if you are a coder or not. Hopefully this helps those who are not traditional coders achieve their goals at super lightening speeds. Any questions please leave a comment below, and yes there are hidden mickey’s. You can find me on Twitter, Alteryx Community or right here. Thanks for catching up with us.
Thanks for the shout out. I’m glad to be part of your journey. See you in London soon. The thrill…