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.
Using public data on schools, I want to see which streets are within a radius of X (the user can change) to be likely to get a place in that school.
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 a 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 showed that some reasonable database knowledge is 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.
Bringing in the Local Authority data is the next step.
Once again use a Join tool, 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.
Use the school longitude and latitude fields in the Create Points X and Y Field area.
Next connect the Trade Area tool.
I want to create a radius around each school of 0.3 miles. To do this set a Specific Value of 0.3 for the Units of Radius (Miles).
Use the Browse function and see 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.
To finish use the Output tool and create a Tableau Data Extract (*.tde) in a location of your choice.
The final Alteryx workflow is below, creating Tableau Data Extract as the final step.
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.