Alteryx for #MakeoverMonday

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:

car data table.png

Of course that’s not going to be very useful and what I wanted to have is this:

car data table new.png

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:

alteryx workflow.png

So what’s going on in this workflow?

  1. Input tool: I’m grabbing the original Excel file
  2. 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)
  3. 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:alteryx workflow2.png
  4. Filter: I want to get rid of any empty rows, so I filter for ‘Car Type is not empty’
  5. Formula: I add 3 calculations where the filter result is true:  workflow3.png
    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.

  6. Select tool: Tidy up time: I select only the columns I need and unselect the rest
  7. 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 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s