Posted by We all know that I love Tableau (it even rhymes!) and I use it pretty much every day to analyse data, create vizzes, publish dashboards and so on.
But I also, would you believe, use Alteryx whenever I can. Most of my need for Alteryx is driven by Makeover Monday datasets as they don’t often come to us neat and tidy and ready to use. A lot of the time the clean-up is minimal, so I haven’t really written about it in the past, but this week’s dataset needed a few more adjustments before I could publish it for everyone to use.
Okay, calm down, it’s no mind-blowing trickery at play here, but I certainly appreciated Matthew’s help in getting the workflow up and running. At this point I should declare that my Alteryx skills are actually very rudimentary. So far I’ve used it when I needed to and I’m definitely rather clumsy when putting together a workflow. It’s something I want to improve on, so having people like Matthew and Naledi around as well as access to our friends at Alteryx should help me make a lot more use of the tool.
Back to the story: this week’s dataset for MakeoverMonday came in the following format:
Of course that’s not going to be very useful and what I wanted to have is this:
So what did I do? I ‘phoned a friend’ and asked Matthew Reeve for his help. He is one of my colleagues and knows Alteryx really well. Plus he happend to be in Germany last week, so I asked him for 10 minutes of his time (we were done after 13min).
Matthew talked me through the steps I had to create in my workflow which resulted in this:
So what’s going on in this workflow?
- Input tool: I’m grabbing the original Excel file
- Transpose: I’m transposing the fields, so that I keep the car type and export vs production columns and generate a column for months and values (number of cars)
- Text to column: As my ‘Name’ field (the one containing months) didn’t have month names, but the format of ‘ 2008, 2008_2, 2008_3 etc.’, I split this field to get the year and months separated, resulting in the following:
- Filter: I want to get rid of any empty rows, so I filter for ‘Car Type is not empty’
- Formula: I add 3 calculations where the filter result is true:
The first formula adds a ‘1’ as the month value whenever there is null in the data (January entries didn’t have a month number after the underscore in the above steps, so we need to create consistency)We also create a date field with a proper date format
And lastly, there were some typos in the dataset, which needed to be fixed.
- Select tool: Tidy up time: I select only the columns I need and unselect the rest
- Output tool: I chose to create a .tde and an .xls file which I then uploaded to the Makeover Monday website
So there you have it, an Alteryx workflow to help shape and clean up the data for the Makeover Monday community.
I hope you found this useful 😉
