Retrieving StatsWales Population Projections via API and Download Tool

I am trying to download the Population Projections found on the StatsWales website via their API and having difficulty, so I am wondering if anyone has managed to do this successfully or fancied the challenge.

Ideally, I'd like to retrieve the counts with the following dimensions setup, so it is expanded by Age and Gender, for both 2019 and 2020: https://statswales.gov.wales/v/HwIo

However, I'd be grateful if I could just get the data into Alteryx first, nicely transposed and ready to work with in any format!

The API is open data and doesn't require a key. So far, I have successfully fetched the data using this link http://open.statswales.gov.wales/en-gb/dataset/popu5099 within a Download Tool. I have then used the JSON Parse to split this up but that's as far as I got. I am struggling to now to get any further so that the counts are ready for processing.

As with many APIs, you can add several parameters to the link, to filter the data as you require. Ideally, I'd like to get this setup so the counts are broken down by age and gender. The API supporting documentation can be found here:
https://statswales.gov.wales/Help/Catalogue#APIEndpointsExplained

Thanks in advance!

Developer Tools

API

Download

Accepted answers

BrandonB

This should get your data in the format that you need for further summarization and processing. Let me know if this works for you! Just a quick JSON Parse, filtering out the metadata row, doing a text to column on the JSON Name column, and then doing a crosstab to put it into a nice table.

download and parse.png

download and parse.yxmd

grossal

I checked out our other URL. You need to disable URL encoding in the Download Tool:

I tried it myself and stopped after 30 iterations. Looks like it's working. Could you give it a try?

wales_main-updated.yxzp

All comments

BrandonB

download and parse.png

download and parse.yxmd

al_sweets

Thanks a lot @BrandonB that's a great start!

Any idea why we are just getting a very small sample of the data coming through though? Only looks like data for low ages in 2014. I can see that the final row once tranposed is a "nextlink" which I've tried downloading which I assume returns the next bulk of the data.

Any recommendations on how to proceed, might have to setup a macro to read it all in? Or am I going to just have to figure out setting up the parameters in the link? Ideally would like all ages for just 2019 and 2020.

Update:

I've managed to filter it to 2019 using the browser by adding the following parameters:

http://open.statswales.gov.wales/en-gb/dataset/popu5099?$filter=Year_Code%20eq%202019

However, when used in Alteryx, it return this error:

error en-US Syntax error: character '%' is not valid at position 9 in 'Year_Code%20eq%202019'.

How can it work in the browser but fail in the Download tool?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703