If you’ve been following my blog posts this week you’ll already have learned how to retrieve JSON crime data from the data.police.uk API and then clean this into a useful format for Tableau. Following on from this we want to make the workflow into an app so that the user can select a location and date of interest and have the relevant data returned to them. The next step in this process is to allow user inputs to change the API query. Check out posts one and two so this post will make sense!
Going back to the start of the workflow where the API query is built, we will find that we created fields for each of the parameters we wish the user to be able to change in the formula tool. The API query requires latitude, longitude, month and year. Seeing as very few people know their exact lat/long values, it would be much better to allow a postcode input, this will have to be converted to lat&long for the police API query to work, entirely doable but we’ll come back to this later.
For simplicity we will focus on allowing the user to select the month and year values initially. Here we drag in a drop down tool and name it ‘Select month:’ and set the List Values to be ‘manually set values’, then enter the numbers 01 to 12 on separate lines. For this month selection to change the API query, we need to connect it to the formula tool where the ‘month’ field is created. By dragging the ‘Q’ anchor to the thunderbolt anchor on the formula tool, Alteryx automatically adds an action tool in between. The action tool configuration dictates what should be done with the month value the user selected. In this case we want ‘update value’. Then we need to define which value to update. Drill down the Formula hierarchy into Formula Fields, FormulaField[@field=’month’] and select @expression – value=“‘01’”. This is the instruction to replace the existing value in the month field with the value the user will select. Now do the same again with another drop down tool but specifying ‘Select Year:’, allowing 2011 to 2016 as the values, and make sure another action tool is set to update value on the year field. Clicking the Run As Analytic App icon will bring up the app interface, select different year and date values, then click Finish and check that the data returned was for the specified dates! If so, great – the app works!
So far the user can change the time period of the data they are looking at… ok, but what about location? To make this really useful, we need to allow the user to input a location format that they’ll know, like a postcode. To turn this into a latitude and longitude, and gain some extra location information, I decided to use the Google Geo API and created another workflow similar to what we have already produced. Here it is:
This time I have used text input tools to allow the first and second parts of the postcode to be entered by the user. These update the postcode parts of the Geo API query, which again is returned in JSON format, the workflow is very similar to the workflow for the police API which I’ve already covered. This time we keep some address fields to make dynamic titles in the Tableau viz (these fields are appended to the Police API crime data at the end of the workflow using an Append Fields tool). Most importantly we now have latitude and longitude. The lat/long fields are now used in the new Police API query. Linking all this together gives the following workflow which can be summarised like so:
Now for some finishing touches to the app interface. Clicking on View – Interface Designer (or Ctrl+Alt+D for keyboard shortcut fans like myself) will allow you to change the layout of the input requests that the app user will see. And that’s it! We’ve allowed the app user to input 4 pieces of information, that have been used in 2 API queries, and output a personalised dataset of mapped crime.