As part of my Alteryx training, following on from my starting out with Alteryx, I decided to try and use it for a real world example to test it out. I recently had a baby so have to start thinking about schools. Luckily the UK government make school performance data public and the excellent Guardian Datablog have tidied it up for me (the data is for 2012, hence now out of date, but good enough for my training in Alteryx). The data contains school address details, local authority details, school size, school religion and multiple measures of performance.
I would like to know which schools are performing well – i.e. their pupils have high attainment in their exams. Then I would like to see which streets are within X miles of the school. This will help guide me where to live to have a better chance of getting into a preferred school.
Alteryx has an function called Trade Area where it can show the radius around a point. Making the school the point, this is ideal to see the schools approximate catchment area.
Each purple dot is a school, with the circle around the dot being the approximate catchment area.
Start by getting the data
I began by downloading a couple of those cleaned up files from the Guardian data site. The first contains the school performance data, another containing the relevant local authority details.
To join these data sources I could bring them into a database. Alternatively I could do it in Excel using some sort of lookup (vlookup, index & match, etc) although the volume of data may not make that practical. However the purpose of the exercise is to test out Alteryx. Therefore I want to do all of the data processing in Alteryx.
I began by importing the school data into Alteryx. This was from a csv so I used the Input tool, available in Favourites and In/Out on the toolbar, to import it. This was simple, the Input tool automatically configured the file for me.
Next I imported the local authority data to give me the local authority name. This will be a filter on the Tableau dashboard.
To cut a long story a little shorter, I processed the data in Alteryx, then realised I was missing some important data. To use the Trade Area tool I need the longitude and latitude of each school, while the data I have only contains the postcode. In addition we need the longitude and latitude to map the data in Tableau.
Fortunately someone else has mapped every UK postcode to a longitude and latitude and kindly made that data available.
Join the school longitude and latitude
Bringing the postcode longitude and latitude data into the mix taught me that some reasonable database design knowledge is very useful to use Alteryx effectively. Many of the tools are database commands – Join, Select, Sort, etc
The schools data came with UK postcode formatted as XX00 XXX, while the postcode to longitude and latitude mapping file has the postcode formatted as XX00XXX. Therefore the datasets won’t join correctly. Removing the space from the schools data postcode is the simple fix for this problem, use the Alteryx Formula tool for this.
In this case we want to replace the empty spaces with a zero length string. The ReplaceChar expression is suitable:
ReplaceChar([School postcode], " ", "")
The next step, using the Select tool, remove the data that won’t be useful.
Once the schools data is in a usable state join it to the UK postcode longitude/latitude data, therefore bringing the school longitude and latitude into the school performance dataset. Use the Alteryx Join tool, joining the postcode from the postcode data and the “new” School postcode, where we removed spaces in the postcode.
Bringing in the Local Authority data is the next step. Once again a Join tool is used, this time joining the LA number in the Local Authority data to the LA number in the schools data. Use another Select tool keep the Local Authority Name, along with the selected schools and longitude/latitude data.
Use the Alteryx geographic tools to build the map
All that remains to display the radius around the school is to get Alteryx to create the trade area.
To begin Alteryx has to know which points to use to enable the trade area. In the Spatial section of the toolbar use the Create Points tool. This creates the longitude and latitude points for the Trade Area function to translate. Ensure the school longitude and latitude fields are used in the Create Points X and Y Field area.
I now recommend using the Browse function and seeing your achievements in the Alteryx map view. Setting the map to view using Maps Powered By CloudMade should return the image at the top of this post.
The final project in Alteryx is as follows, with the Tableau Data Extract created.
Thank Alteryx for making that more straightfoward than any other method, open up Tableau and use the newly created Tableau Data Extract to visualise the data.
Finally connect the Tableau data output to Tableau and create this in a Tableau map for more general consumption.