Dashboard Week

It’s Dashboard Week! It’s only day one, and I am already pulling my hair out! We do a project every day, (usually we do one per week), and write a blog for each project.

UK Street Crimes

Today’s project had the following requirements:

1. Use data from the UK Street Crimes API in Alteryx
2. Allow users to select the postcode, crime category, and time range of data they want to see
3. The output of the Alteryx workflow has to open up a Tableau dashboard

API

Before jumping straight into Alteryx to download the data, I examined the API using Postman API App. Postman is handy for understanding how to build-up your API call URL with the different parameters and seeing a preview of the JSON that’s returned. I am getting ahead of myself! Let’s go back to basics; sometimes I forget that just two weeks ago the above sentence was like Chinese to me, zero understanding. My understanding of APIs:

• API = Application Programming interface
• APIs are tools that make it easier for developers to build applications – (in this case, I am the developer, and I am building an application in Alteryx)
• To access the data in an API, we send a call telling it to return the data we want
• Good APIs are well documented and highlight the different parameters you can add
• API call = API web address +  parameters

(DISCLAIMER – the above could be entirely inaccurate, I highly recommend you read the API Wikipedia page)

Looking at the above example API request in the Postman app I could see that:

• The postcode needs to be translated into latitude and longitude parameters
• API can only pull back one month’s worth of data at a time using the date parameter formatted as yyyy-mm
• the category is part of the URL, rather than a parameter that gets added onto the end.

Alteryx

Now I am ready for Alteryx! I know that ultimately I will have a list of API calls, one for each month in the date range specified by the user, and I want to download the data for all of them. Therefore the first thing I did was build a batch macro to download the data. Appropriately named, POLICE!

Next, I needed to create the workflow to build my list of API calls to run through my batch macro. The user needs to be able to input a postcode, a crime category, and date range, so I created text inputs for all of these. My intention is to concatenate all my text inputs into the URL. First I created a category input. There are two fields in my text input tool one with the URL and one with the category. The category field updates based on the user selection in the drop-down box. Then there is a formula tool to create a new URL by combining the two fields.

Next, I needed to add a text input for the user to enter a postcode and somehow translate the postcode into latitude and longitude. After a bit of Googling, I found postcodes.io a postcode and geolocation API for the UK. You enter a postcode, and it spits out all sorts of geolocation info. Brilliant! I decided to build a standard macro to take the text input, download and parse the geolocation data and output just the latitude and longitude.

Lastly, I needed to create my list of dates based on the range selected. I created inputs for start date and end date then used the generate rows tool to fill in all the dates in between.

The final Alteryx piece appends all these fields together, runs the URL list through the POLICE macro, puts the output into the Run Comand tool that saves a .tde and launches a Tableau Dashboard within the workflow.

Last but not least, before moving into Tableau to create the ultimate output of my Alteryx App. I opened the Interface Designer Layout View to add some finishing touches to my app’s user interface.  This is what my app looks like after I added the finishing touches:

Tableau

Now the hard part is finished! The only thing left to do is throw together an amazing dashboard in Tableau!  Hopefully, my dashboard is different from what the other DS5s create since we are all working with the same data. I think I managed to come with a Dashboard that works for all possible user selections in the Alteryx Application: