Can I make Alteryx a Quiz Master?

In 2020 everyone is spending more time at home and having video calls with families, friends and colleagues and some are holding quizzes during these calls. It led me to think was it possible to use Alteryx to host these. The short answer is yes you can! I have to acknowledge this is not an original idea and Dr Tim Rains (DRains) first showed me this was possible in 2018. I did though take this on as a challenge, with the objective of not reaching out to him to ask how he solved this. Therefore on a Saturday morning I came up with the following to make Alteryx a Quiz Master.

It was relatively easy to build a process in Alteryx the below screenshot shows the entire workflow, a text input of words you want your computer to speak and a batch macro. In summary how this works is each row of the text input, is read into the batch macro. Per batch the computer speaks out the text within that row of data, plus also waiting a specified amount of time to give the quiz participants time to write down their answers.

Workflow Overview

The Text Input holds all the text I want my computer to speak, including a welcome message and a list of questions to be asked.

Text Input of words to be spoken

The configuration of the batch macro is really simple with no fields being used in the Group By tab and then for the Questions tab I’m using the field “Questions”, which has all the text to spoken by my computer.

Batch Macro Configuration

The macro itself has few tools a Macro Input a Filter tool which is being used  to select the correct batch every time it runs. Then the next tool is WaitASecond which I can specfity the amount of seconds before the data in the macro is passed downstream in the macro.

Batch Macro Overview

The WaitASecond tool is actually a CReW Macro released in 2016 more information about this macro can be found here.

WaitASecond Tool Overview

In the Formula tool I am creating two columns one is a VBS_script with the Question column with the text to be spoken, being inputted as a parameter per batch. The second column is a File path location to output the file later in the process.  

Formula Tool VBS Syntax Creation

Next in the process I use a Select tool to remove unwanted fields however it not a requirement. I use a Blob Convert tool, to convert the VBS_Script field created in my Formula tool into a Blob data type.

Blob Convert

The reasons for this conversation is shown in the Blob Output tool, where I output the Blob field as a VBS file. With also the option of “Modify File Name By:” with the drop down of “Replacing Entire Path with Field” using the column “Field”.

Blob Output

The result of this Blob conversation and output is a VBS file, where the highlighted text is being replaced per batch. This VBS file alone will play once it’s clicked however I want the file to be played automatically without needing to be clicked.

VBS File Syntax

This is where the Run Command tool comes in and executes a batch file, which calls on the VBS file I’m creating per batch. These makes my computer speak the text within that VBS file every batch.

Run Command Overview

The syntax within the bat file is very simple it’s just start and the name of the VBS file.

Batch File Syntax

The result of all of this is when I press run in Alteryx Designer is that Alteryx will now become a quiz master and starts to fire off questions.

Hope you find this blog helpful and seen how easy it is to turn Alteryx in a quiz master. Please leave a comment or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Using Alteryx to Map Sales Territories

Have you ever needed to create sales regions? I am going to show you in this blog how easy it is to create sales regions using Alteryx.  Typically a team will do a pencil exercise and allocate stores and then hand it off to a GIS team to map the regions to ensure they make sense. In my fictitious scenario I am mapping the sales regions of Giuseppe Stores so that area managers know the territories they cover.

My dataset is a list of stores with a centroid point and the regions that have been allocated via a pencil exercise, from the list of stores I will create sales regions utilising the tools in Alteryx.

Giuseppe Stores Data Input

To turn these points into the polygons of the sales regions we wish to generate, is quite simple and can be done using a trade area tool. The key is to generate non overlapping trade areas or voronoi polygons.

Non Overlapping Trade Area/Voronoi Polygons

In the trade area tool configuration window you just need to tick Eliminate Overlap and specify the radius to a high enough value. This will generate non overlapping trade areas as seen above however as you can see they don’t quite yet look like sales regions yet.

Trade Area Tool Configuration

The key is to use the summarise tool which is part of the Transform Category in Alteryx designer but has the ability to perform a number of spatial functions. In this instance we need to use the spatial function of combine and grouping by the regions field. This will combine all the spatial objects by all the groups of the regions.

Combined Regions (Summarize Tool)

The numerous trades areas have now all be combined however as you can see they extend into the sea plus into other countries. The next step will be to clip the regions to the extent of the UK, to remove the excess areas of the sales regions that I am generating.

I simply downloaded a shapefile of the UK and brought this into my Alteryx workflow. I then appended the shapefile to my regions. Using the Spatial Process tool I then selected create an intersection object, this will create a polygon field of the areas of the UK where both polygons overlap. Therefore cutting all the areas that extend to the sea and other countries outside of the UK.

Clipped Sales Regions

As you can see now the regions are clipped to the UK extent and look sensible and can now be turned in a thematic map in order to be presented.

Due to the odd shape of the UK I had to perform some manual inputs to stop regions crossing rivers, or the sea between Northern Ireland and Scotland. I did this by using the Map Input tool and digitised some fake stores to push the boundaries of the regions, when I generated the non-overlapping areas.

Digitsing Map Input Tool

For speed when making this blog I digitised lines and named them similar to the region names then I used Poly-Split tool to break the line into a series of points and then used a union tool for my input of stores and my digitised points together before I generated the voronoi polygons. 

Poly-Split Line to Points

While I would recommend an approach of drawing lines for the manual interventions, when going through multiple iterations of generating sales regions. When building the final sales regions and especially if the output regions is being outputted into SQL which requires the geographies not to be broken, then I would digitise using points and check you don’t have any multi-split or broken polygons in the regions you are producing.

Sales Regions for Giuseppe Stores

By putting the regions into Tableau and creating a thematic map you can see that I have successfully generated sales regions from an input of points that can be presented back to the area managers to show them what territories they cover.

Hope you find this blog helpful and seen how easy mapping regions are in Alteryx. Please leave a comment or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

Advent of Code

Christmas, Christmas, Christmas…. Well its Christmas time, pretty lady and code is falling to the ground! (Christmas Chronicles anyone? My girls loved this scene)

Yes its that time of year when we all have get competitive and have a great time playing against each other across the world. We are already moaning here in the UK as its well against us here with the release time of the challenges, 5am. But I am not doing to bad after the first day of Advent. I can guarantee with the week I have ahead, I will not be anywhere near 10th so I am going to take this small victory whilst I can. 🙂

Advent of Code Website

he Private Leaderboard

Why should I take part in AoC?

  • Fun
  • Challenging
  • The theme is always great for playing along, this year’s theme is SPACE
  • You can complete it in any programming language you like
  • You can complete it in Alteryx
  • You can try and complete it in Alteryx using BaseA (see below)
  • You can join a leaderboard with the ACEs and beat us! It’s all down to timing… you could wake up at 5am UK time and smash it out.

What on earth is BaseA?

There is a concept amongst ACEs that I want to let you in on, called “BaseA”. This is when you complete a challenge like “Advent of Code” without using code, such as Python, R or the Run Command tool, no SDK usage or using the download tool to get answers from an API as these make doing these challenge far easier. Therefore they are BANNED. It is achieving these challenges using Alteryx core tools that allows us to get our heads around the problem in a different way. It also helps us push Alteryx as far as it can go. More info on James Dunkerley’s post.

So you are a first timer… how do you get started?

Firstly log in to the leaderboard, set up by our very own James Dunkerley: https://adventofcode.com/2019/leaderboard/private/view/453066 using the code: 453066-ca912f80 under the join a leaderboard page, looks like this:

Then you have to view it to see the wonderful stats as above.

Once you are linked in to the leaderboard and logged on via Twitter, GitHub or Reddit. You can then get started on the puzzles.

Now lets look at a puzzle, no I am not going to give you any answers! But there may be clues, so if you are not wanting any tips at all, please stop reading. Come back when you are done, good on you!

ARE YOU SURE?

Welcome to my NOT so HIDDEN Mickey

Well you have scrolled this far because you are after some tips with Part 2 of Day 1, because the first part is simple maths, which is explained in the text.

Puzzle – Part 1
Puzzle – Part 2

Part 2 – however requires some thought and for those who are not strong in macros, you may feel like you have fallen at the first hurdle. However I am going to point you to some really useful resources to help you out, all can be found on our fantastic Alteryx Community.

Treyson’s post on macros here is really useful: https://community.alteryx.com/t5/Engine-Works-Blog/Hello-Iterative-Macro-My-Old-Friend/ba-p/420308

Weekly Challenge #12 – Creating a HR Hierarchy: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

A post by PaulT that has some great info and visuals to support those who love visuals: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-create-an-Iterative-Macro-The-Collatz-Conjecture-Alteryx/ta-p/108830

I hope these get you started and enjoying Advent of Code, its great to learn, push yourselves and achieve something you never thought possible. If you find it too easy, try again in Python or R and challenge your friends.

Enjoy this years Advent of Code, see you on the leaderboard!

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.