This blog will revolve around some of the things that I learnt in Alteryx. I decided to do this by showing my solution for two of the Alteryx Challenges. The Alteryx Challenge is a weekly challenge that Alteryx sets you to complete. There are currently over 300 challenges to do. I will be showing the solution to challenge 1 and 2. Before I start, there are many ways to solve a problem so feel free to go to the Alteryx website and check out how other people did it and see if you can find a more efficient way to get the answer. Every Alteryx challenge gives you the input data and the output, so you know where you need to go but you need to figure out a way to get there. It is a great way to test your skills. I plan on completing all Alteryx challenges. Let’s begin with challenge 1.

Challenge 1: Join to Range

This challenge is classed as intermediate and is good practice if you are aiming for your core certification.

Here is the challenge from Alteryx.

A company in Australia has source data which is made up of a series of postal codes (eg. 2000, 2001, 2002 etc.) amongst some other data fields. They have a separate reference table which contains postcode ranges (eg. 2000 to 2002) which they would like to use to match/filter their main data.

Each Customer Record needs to be joined to the Lookup table based on a Postal Area Ranged region. Then finally summarize the customer data by Region, Sales Rep, and Responder, then a count of customers.

Check and see what the result should look like by looking at the data labelled ‘Output’.  Your mission is to take the input files and blend them, so your result matches the output shown.  Good luck!

The first thing I did was separate the postcode ranges in the reference table into separate columns and then renamed them for future reference. I did this by using the text to columns tool, which you use to separate strings into separate columns, by setting the delimiter to ‘-‘ and I asked for two columns since the range column had two dates. This called the columns 1 and 2 and I used the select tool and renamed them to Range 1 and Range 2 respectively.

Now that we have the dates in two columns, a third column needs to be created that has those two years plus every year in between. I got stuck on how to do this, so I used Google and found out about a new tool, generate rows. I used this tool to create a new field called ‘Postal Area’ starting with ‘Range 1’ and adding one to the value each time and stopping when ‘Postal Area’ is equal to ‘Range 2’.

Next, I used the Join tool to join this table with the original table, I joined on ‘Postal Area’ as that is what the challenge asked for. The select tool was needed next as there were many columns that were not needed, and I also ordered them in an order that matched the solution.

The final table is now ready to complete the challenge. We were asked to summarize the customer data by Region, Sales Rep, and Responder, then a count of customers. This is done by using the summarize tool and grouping by Region, Sales Rep, and Responder and then doing a count of customers. Once this is done you can add a browse tool so you can see your final solution and it should match the solution provided in the workflow.

Challenge 2: Preparing Delimited Data

This challenge is classed as basic and is good practice if you have only just started using Alteryx.

Here is the challenge from Alteryx.

Many products will export textual data with delimiters such as quotes. This is done so that strings can contain delimiters or control characters within them. Having more than one type of delimiter can be hard for ETL programs to interpret.  In the input text file, there are two different delimiters (double quotes, single quotes) and they surround different data types.

Use Alteryx to strip out the delimiters as superfluous and format the data as represented in the output.

The first thing that I did was separate the poem, poem id and poem read date. They were all separated by commas so I used the text to columns tool to get them in different columns so they can be formatted properly as well as match the desired output. Below is the table that I ended up with after this step.

Comparing the above table with the final table that I am trying to create I found that I need to remove the quotes from around the poems and remove the quotes around the dates as well as changing the format of the date. This can all be done with the formula tool. I found out on google that there is a function that will remove quotes and using that the first two formulas was the remove quotes formula for row 1 (poem) and the same for row 3 (poem read date). I then did a third formula on row 3 (poem read date) to change the format of the date. This can be done with the datetimeparse formula. The formula required the date column as well as the format you want the date. To do this I used Alteryx documentation to write the specifiers needed to match the date in the desired outcome. I will provide the link to that page.

https://help.alteryx.com/20223/designer/datetime-functions#:~:text=Alteryx%20uses%20the%20ISO%20format,editor%20or%20the%20DateTime%20tool.

Below is also the formulas and the table that it created.

In order to get the final answer, some formatting needs to be done. Using the select tool, I renamed the columns and chose to not show the original and now my final table matches the desired outcome.

These challenges were quite simple, but I wanted to start out light for those who are not that experienced with Alteryx. I plan on doing a similar blog but with much harder Alteryx Challenges. This is just an introduction into the challenges.

 

Hope you enjoyed my blog.