Preparing a CSV File with Alteryx

by Jamie Gough

Today was our introduction to Alteryx and we ended the day by editing two CSV files that were unreadable with Tableau. By using a similar data set, I will run through a couple of functions that can be used to make such data readable by Tableau.

Below is an image of the CSV file I created, gender stats, using OECD data. To the left is how it looks in Excel and on the right, is Tableau’s observation of the data.

As you can see the data in its current format can’t be read by Tableau. It’s unable to distinguish columns, due to the table starting four rows down, and fails to recognise any data types. Beneath, is a screenshot of the Alteryx workflow I used to convert this file to a readable one in TableauHere’s a quick description of the functions that were used at each stage of the workflow:

  1. Input Data – Loaded the original CSV file.
  2. Sample – Removed the first 3 rows of data
  3. Dynamic Rename – Assigned the top row, originally the 4th, to field names
  4. Transpose – Swaps years from rows to column to enable the next stage
  5. Data Cleansing – Removed square brackets from around the years
  6. Filter – Was used to remove rows with ‘..’, which indicated missing data
  7. Select – Renamed the title of rows to match the data and removed unnecessary columns.
  8. Output Data – Created new TDE file with edited dataset.

To complete these 8 functions, including the creation of a new file, Alteryx only took 1.6 seconds and now I have a usable file to analyse in tableau! Here’s a screenshot of the finished product in Tableau.

Tips for Making the Most out of the Data School

2 mins read

Thu 28 Sep 2017

Alteryx Machine Learning Theory: Gradient boosted model

3 mins read

Mon 04 Sep 2017

Dashboard week day 3 – Building transport maps is fun!

2 mins read

Fri 11 Aug 2017

Dashboard week day 2: Brewery and Beer Style Recommendations

2 mins read

Wed 09 Aug 2017