Thursday, July 4, 2019

What I've learnt completing 100 Alteryx Weekly Challenges

So I have finally earned my Community badge for completing 100 weekly challenges.

Community Badges

So while hitting this milestone I thought it would be worthy analysing what's involved and highlighting some hints and tips I've picked up as part of participating in the weekly challenges, as I find these are a great learning resource for covering areas of the software which I may not ordinarily utilise as part of my day job!

Weekly Challenges are not that time consuming!

So firstly I want to highlight that the Weekly Challenges are not too time consuming, and most solutions need only a few tools to complete.  Looking at my solutions, the simplest workflow contains just one formula tool (excluding the text inputs, comment boxes and the browse tools) to the most complex requiring 64 tools (Challenge 165), with an average of 12 tools. I don't know my total time to complete the challenges, but I did my first challenge in August 2018, so it's taken me 307 days to complete 100 challenges, with a few intensive stints and some periods of not many completions.

There's several challenges which I've completed in under 2 minutes, and along with a couple of colleagues at work we do the challenges Grand Prix style to add an element of competition.

It allows you to discover tools you may have never used before

So if I take a look at the tool categories I have used across these 100 challenges*, I can see that the majority fall under categories like Preparation, Join, Transform and Parse, but I've also used things like the Calgary tools which I've never used outside of the Weekly Challenges. Capture2Capture3 *at the time of writing I've actually completed 102 challenges, and the macro results therefore reflects this.

Now some tips and tricks

I've been back over some challenges to identify some techniques which may be considered intermediate level to assist with transformations. What is great is once you've used these techniques once and understand how they work you'll end up going back to them again and again.

Text to rows

Coming from Excel, many Alteryx users will be familiar with text to columns where you split a string based on a delimiter to new columns (common where the input is a comma delimited file). However one useful function in Alteryx is split to rows, I have used this on Challenge 139 where a benefit of using split to rows is to keep the data in one field, so the numbers can be parsed out in the next step. Capture4Capture5

I've also talked about how using splitting to rows can make a workflow more dynamic in another blog post, as it doesn't require knowing how many columns you need to split the field into.

Regex tokenise

So similar to splitting to rows on a delimiter is using the regex tool when there might not be a delimiter. In the case of Challenge 59, where I needed to whether words in two fields were an anagram of each other. To solve this, I split every character for each record to a new row, and from there sort the letters and then concat them back and if they match then it's an anagram.


The modulo function is used in Tableau when building small multiple charts, and I used it a lot when doing Advent of Code. What modulo gives you is the denominator of the fraction in the remainder as part of a division. For example the modulo of the value 9 when divided by 4 is 1 as 9/4 = 2 1/4. In Alteryx this is done as part of the formula tool and the syntax is mod([Field],divisor). I used modulo in Challenge 21 to restart the numbering of the records every 12th record as this represented each month of the years 2007 - 2008. So here, I had records 1 - 24 and what I wanted was the numbering to go 1 - 12 for 2007 and then repeat 1 - 12 for 2008. So here the formula tool was used to update the RecordID field with the following function:


This then allowed me to join the numbers 1 - 12 on a text input to get the months January - December.

Reverse String

So this is a string based function which reverses the order of every character in a string. I found this really useful in Challenge 40 which involved parsing web html data. Again coming from Excel I'm familiar with using Left, Right and Mid (substring in Alteryx) functions to return a certain number of characters, but what I wanted to find every character after the last '>' however the position of this varied by record. An example of the full string is as follows:
So from this I needed a way of identifying the position of the > before Brett Abernathy so that I could get the Doctor's name and discard the rest of the string. The resulting formula was as follows:

substring([Doctor Name],IIF(FindString([Doctor Name], ">") > -1, Length([Doctor Name]) - FindString(ReverseString([Doctor Name]), ReverseString(">")), -1),length([Doctor Name]))

So this is used FindString to find the position of the ">", but as it works left to right you reverse the string so the last ">" becomes the first.

So to round this all up

I hope you've found these techniques are useful and will be able to use them, so go ahead and use them in your day to day Alteryx work and in weekly challenges and it encourages you to attempt more weekly challenges to discover some of your own new techniques.        

No comments:

Post a Comment