This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
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.
I've managed to filter it to 2019 using the browser by adding the following parameters:
This is likely an API that uses pagination, meaning that it downloads a certain number of records and then provides the link to the next set. The way to do this in Alteryx is to leverage an iterative macro which leverages the url for the next set as what to iterate through on each run. When that passes through, the next data and next link will continue to be passed through until all of the data comes through and there isn’t another next link. I will set up an example for you this next week if nobody else builds this out by then.
Obviously the ideal method would be to filter it beforehand to avoid unnecessary calls to the API. Given I only want years 2019 and 2020, it seems undesirable to retrieve data for all the other years up to 2039.
Do you have any advice for the syntax error when filtering, which seems to work fine within an internet browser but fails in the Download tool?
However, it still only returns a total of 14,001 transposed records for me, stopping after just 14 iterations. It doesn't get further than Age 11 population projections for only year 2014, and I'll need all ages for 2019/2020 so quite a way to go. The last row of the final iteration is entirely null for all fields, with no "next link".
@grossal Thanks for the tip regarding unselection the encoding option, this now works outside the iterative macro.
Using the macro, mine actually got up to iteration 49. It then continues to run indefinitely and throws the following error from iteration 50.
The macro should maybe check for the Null field then so it can dynamically stop in future. As a workaround, I set the macro to limit to 49 iterations and this seems to work perfectly. I've looked at the data and it's exactly what I was expecting.