Introduction

APIs are an essential tool for accessing data and at first glance may seem extremely confusing.  I thought so at least and up until this last week at The Data School I was dreading having to learn about APIs.  However, I was pleased to find out that they can be a lot easier than one may think and are very useful when wanting to quickly access most data sources online.  In this blog I will take you through the entire process of accessing a data source online using an API, through to exporting that data as a .CSV.  I will be accessing thesaurus data from the Merriam-Webster online dictionary which can be found here: https://dictionaryapi.com/products/json.

 

Testing The Connection

Firstly, it’s important to make sure the connection is working before trying to do it in Alteryx.  This will save you a lot of hassle and using a tool like Postman is a great way to do so.  After signing up for a Postman account, you will need to find the API URL you will be using.  In this example Merriam-Webster provides unique ones with API Keys in the link so they can do their own authorization.  On their website you will see the following link with your individual API Key on the end.

 

 

In this case that as all you will need but I will go through the steps of using a key in Postman in case you are using other data.  In Postman you will need to start a new request and enter the above URL, without any key into the text box.  Below the URL box you will need to type the Key into the ‘Value’ section and it will provide a complete API URL for you to use in Alteryx.  Once the link is complete you can press send and at the bottom of the page a message will appear, either showing you the JSON data or an error message.

 

 

Moving Into Alteryx

Start by dragging in a ‘Text Input’ tool as we will be manually entering the URL provided above.  Once in the tool, in the table on the left you will create a column named ‘URL’ and enter the link above into the first row.

 

 

After that you can drag in a ‘Download’ tool and have the Text Input running into that.  Immediately after the Download tool, drag a ‘JSON Parse’ tool in and attach that after the Download tool.

 

 

As the screenshot above shows, you want the Download tool’s Field to be set to the URL column.  In the JSON Parse tool you want the JSON Field to be ‘DownloadData’ which is the output from the Download tool.  The output from the JSON Parse tool will be the data.  There will be four columns, the original URL column, the Download tool output ‘DownloadData’ and the two new columns which contain the data, JSON Name and JSON Value.

 

 

Formatting the Data

Now obviously the data is not currently formatted in a usable way.  This is one of the challenges of using a JSON file.  To fix this, we will use a Select tool, to remove the Download Headers column, and then a Cross Tab tool.  We will want to make the JSON_Name column all individual headers as you can see they explain the data, they are not values.  So in the Crosstab tool we will group by URL, Change Column Headers with JSON_Name and set the values to JSON_Values.  The method for aggregation will be concatenate.  The output will now show the data in one row, the row representing the word ‘test’ in this example.  And all the columns being features of the word.

 

 

Now there are obviously way too many columns to work with here and in this example we are wanting to create a Thesaurus so we will not be needing a lot of the other columns.  There are many ways to get rid of the columns however I have manually deselected the columns I don’t need using the select tool and searching within there.  For example, I don’t need the definitions so I searched for ‘def’ and deselected all columns including it.  I did the same for ‘list, I went through the data and found what I didn’t need and removed it.  In the end I was left with only the columns including ‘syns’.  This can also be done with filters, in this case however, I found it easiest to do it with the Select tool.   We now have only the necessary columns, showing the synonyms for the word ‘test’.  Further formatting can be done to make it look better however we now have a complete workflow of pulling the data we need from an API.

 

 

Adding User Controls

Now, lets say we want to allow users to enter a word and get a list of synonyms back.  To do this we can turn the workflow into an app. We don’t need to do much and will only need to change one word in the URL.  To start, we will need to bring in a Regex tool and insert it between the Text Input and the Download tool.  This will allow us to select the search word and replace it with whatever we like.

 

 

As the incoming URL has the word ‘test’ as the search word we can select that to be replaced.  In this example I have chosen to replace it with word, but that will be dynamic as we will now add a user control.  First, drag a ‘Text Box’ tool in, above the Regex tool and then connect the output to the lightning bolt on top of the Regex.  This will make an action tool appear and we will now be able to set the parameters for the user to choose what they search for.  Right now the workflow should look like this:

 

 

In the Tool Box window you can enter what you want the user to see when they open the app, I have chosen; ‘Enter a Word to Get Synonyms’.  Now if you click on the action tool, the one between the Text Box and the Regex.  You will see a menu on the left.  This is where we select what will be changed when the user enters something into the text box.  As we have set up our Regex to change the URL to contain ‘word’ currently, we will want them to be able to change that, thus changing the URL and changing what word and it’s synonyms are returned.  This can be done with the following configuration:

 

 

Setting Up The App

Now that the back end is set up we will need to make sure the user facing side is running smoothly.  First we must have an output so drag a browse tool onto the end of the entire workflow.  That output is what the user will see in the app.  Next we need to select that as the output and to do this we can open up the Interface Designer from the view dropdown.  Then click on the settings Properties wheel and check the output browse box.  You can also enter any personal messages you wish the user to see here too.

 

 

To do a final test of the app you can click the wand in the top right hand corner of the Alteryx window and it will appear.  In this app window you can try type in words to test the app however if you have followed the steps it should work fine.

 

 

Conclusion

Hopefully this blog has made APIs easier to understand.  Unfortunately every API will give you different information so this formula won’t work for all.  It is however, a good example of what to expect and as you do more APIs you will understand what needs to be done.  The App was just a bit of a bonus but as you can see it opens up a whole new level to what APIs can do.  Thanks for reading this blog, keep an eye out for the next one!

Mikael Nuutinen
Author: Mikael Nuutinen