Alteryx Tool: Download
If you have spent any time looking for public data you will no doubt be aware that there is a lot of data that has been made available via API’s. Working with API’s can be easy enough but the Alteryx download tool can make it much easier, especially if the output of the API is a JSON file.
Before we get to the download tool itself we need to get the API string into the tool, this could be from any datasource, but here i have used the text input tool to store and then feed the API call into the Download tool.
The API i’m using here is the TFL Unified API, avalible from https://tfl.gov.uk/info-for/open-data-users/
The download tool itself requires a field that contains the API call as the input to the tool:
There are various options for output including outputting to a field/BLOB or outputting to a file, for this particular API it produces a JSON output, a format that we are going to manipulate within Alteryx.
For more complex download calls you can programatically generate the API call from a base string and some other fields that Alteryx can assemble into a query string:
The output from the download tool will look like the following:
The download will be appended to the row, and will include the data and any headers to the call.
We then need to parse the DownloadData to get the actual information we have downloaded, in this case the status of Underground DLR and TFL Rail Services, doing so gives us the following output, again to the left we can see the original input to the flow, then the Header and then the JSON Fields, With JSON_Name being a combination of RowID and Column Name.
Our final steps are all to clean up the output and put it into a proper table format:
The formula tool adds a special character as a separator between the rowID and Column name this is because “.” which was the original separator is used as part of the field name for some fields. We can then use the text to column tool to split the row ID from the Field Name Column. Then we remove the unneeded columns and finally use the crosstab tool to rearrange the data into a better format for analysis, giving us the final output below:
Those of you familiar with tableau will know that there are tools within tableau that you can use to pull from a web API into tableau, namely the web data connector, however this process does involve some measure of web coding ability to set up the connector, whereas the download tool as hopefully you will have seen above, is a much easier way of accomplishing the same task.