Using an API in Alteryx to retrieve crime data

by Peter Gamble-Beresford

In week 3 of The Data School our task for the week was to build an Alteryx app to retrieve data from an API where possible. Connecting to an API opens up all sorts of new data sources, and isn’t necessarily that difficult to implement, so it’s worth taking the time to learn. Over the next 3 blog posts I will detail how I made my app as a rough ‘how to’ guide for each part. These parts will be: 1) how to use an API to download data in Alteryx; 2) how to clean the data to make it usable in Tableau; and 3) how to allow user input to control the API query. By the end of these posts you’ll have a good idea of how I built my app, and hopefully have learned how to do a few new things in Alteryx too.

My app utilised the data.police.uk API, in order to build a user-defined map of local crime. It was a great starting point to understanding how APIs can work, and how Alteryx can handle the importing and cleansing process before you are able to visualise the data.

So how do you use an API? Well, the website’s API documentation is a good place to start, before even opening Alteryx, you should familiarise yourself with this documentation first (see the Police API documentation here). You should find an explanation of how the API queries its database (the question or command you are giving it), what it returns given the query (the answer it gives you), and the format of the output you are given.

Break it down:

The question in this example can be defined as a url with certain parameters: latitude, longitude, month and year. The answer is a JSON formatted data output. Just pasting the query into your browser will give you a quick view of the raw output, if you change the values (lat/long & date) you will see this output change accordingly. This is the data that Alteryx will receive later in the process.

We could just put this url into Alteryx to query the API, but if we want a user to be able to change the query later on, it is best to break the query into its constituent parts by creating fields. This is important as when it comes to making the query usable in an app, some parts of the query need to change, but the query structure must remain specific in order to work properly.

Break the URL into its components

Once we’ve worked out what parts these are, we simply construct them as new fields in Alteryx and stitch them back together again to rebuild these various parts into a url Alteryx can use to retrieve the desired data.

Now build the workflow:

Workflow

  1. Text input tool: create a new field called ‘base url’ with the first part of the url up to the first variable part
  2. Add a formula tool creating each of the variable parts with their values as strings
  3. Create a field called ‘query’ which reconstructs the url as a string
  4. Connect to a download tool using ‘query’ in the URL field box
  5. Click run and check the output! The data will be in the ‘DownloadData’ field

That’s it! The data is in. Next we will find out how to prepare the data for use in Tableau, then we will return to the API query and make it into an app by using interface tools to allow the user to enter their own year and month values.

Avatar

Peter Gamble-Beresford

Tue 14 Nov 2023

Thu 24 Aug 2023

Fri 21 Apr 2023