API for Beginners

[Nicola_Mahood]

Hello,

When it comes to APIs I am a little green around the gills, and it hasn't been previously utilised in my authority.

I started with the input text tool, and I got that working but there is an API key and I cannot make that work within the Download configuration.

Has anyone any advice on how I can do this?

I am trying to use APIs to gain access to DWP Data from Stat-Xplore.

Thank you

Nicola

[Hannah_Lissaman · Accepted solution]

HI @Nicola_Mahood 

You need to enter the API Key in the headers tab as follows

I've built you a quick example to download and parse the schema, attached. You can see the other available options that you can download in the API docs here - just change the suffix of the URL: 

https://stat-xplore.dwp.gov.uk/webapi/online-help/Open-Data-API.html

Please mark as a solution if this helps 🙂

[Nicola_Mahood]

Thanks Hannah,

That is brilliant, is this something you have used before then? I have tried for table and it doesn't allow it. 

[Hannah_Lissaman · Accepted solution]

Hi Nicola

Working with APIs always requires a little trial and error! I've taken another look at it and have some tips for you. However it will probably take some exploring to get to exactly what you want. The first step should always be to read through the documentation carefully, and understand all of the information you will need to pass to the API so that it can return data to you.

To query a table, you need to build a query as described here under 'Request Body':

https://stat-xplore.dwp.gov.uk/webapi/online-help/Open-Data-API-Table.html

There are a few steps to do this. 

• Use the /schema API call to find your table and field IDs. Start with the basic example I posted. If you look at the results, this returns a list of folders with a URL corresponding to each. Find the one you want to use, and replace your URL input with that URL. This will return a similar list, but this time with a list of databases within that folder. Repeat the process with the URL for the database you want to query. You should now have a list with URLs corresponding to the database, and its measures and fields.

The relevant part of each URL is this bit: https://stat-xplore.dwp.gov.uk/webapi/rest/v1/schema/str:database:CA_In_Payment_New

As a minimum, you will need this section of the URL for your database, your chosen measure (usually includes 'count' or 'mean' etc.) and the dimensions you would like to split by.

• Build your request. I recommend doing this in a text editor so it is easy to read - I use Sublime Text, but you can even use Notepad if that's what you have. Start with the example from the documentation, and then swap out each section for the relevant bits. I've highlighted the parts you need to edit. You can also copy their syntax if you want to add more items in a list, e.g. more splits.

My edited version looks like this - I removed the optional 'recodes', and created a query in the Carers Cases in Payment database for count of payments by gender and duration of claim:

{
    "database" : "str:database:CA_In_Payment_New",
    "measures" : [
        "str:count:CA_In_Payment_New:V_F_CA_In_Payment_New"
    ],
    "dimensions" :
    [
        [
            "str:field:CA_In_Payment_New:V_F_CA_In_Payment_New:CCSEX"
        ],
        [
            "str:field:CA_In_Payment_New:V_F_CA_In_Payment_New:CTDURTN"
        ]
    ]
}

• Configure a new Download tool. First add a new Header to define the Content Type: 

Then in the Payload tab apply the highlighted settings and paste your query text into the box at the bottom:

I am sure that, as I said, you find some trial and error is required to get to exactly what you want, but hopefully this is enough to start you on your way! I've uploaded my example workflow with the additions.

[Nicola_Mahood]

Hannah,

Thank you so much, I should definitely be able to use this to learn! I find the guidance on the Stat-Xplore site to be lacking and obviously it isnt Alteryx focussed so I was utterly lost!

Thanks again!

Nicola