In this blog post, I will talk you through how to make a call to the Marvel API in order to access data regarding Marvel Comic book characters.
The first thing we need to do is to sign up to the Marvel API website in order to obtain 2 API keys:
This is the base URL which API calls will be made against
API Keys:
When subscribed to the API portal, Marvel provides two API keys for each user. A public API key and a private API key. Each key is a long string of characters. Once we have our keys we are ready to go
Insert a text input into Alteryx and configure it as below
At this point, copy the text input tool over to a new workflow as eventually we will be creating an iterative macro to bring back more records. Then convert the text input tool in the first flow to a macro input and we will keep working in the macro for now.
When looking at the documentation to access the API, we need to pass through some additional parameters:
- ts - a timestamp (or other long string which can change on a request-by-request basis)
- hash - a md5 digest of the ts parameter, your private key and your public key (e.g. md5(ts+privateKey+publicKey)
For example, a user with a public key of "1234" and a private key of "abcd" could construct a valid call as follows: http://gateway.marvel.com/v1/public/characters?ts=1&apikey=1234&hash=ffd275c5130566a2916217b101f26150 (the hash value is the md5 digest of 1abcd1234)
We can use a formula tool to create these additional parameters like below:
Now we have our parameters we can combine these all together in a new URL field using another formula:
Now we are ready to download from the API by inserting a download tool and configuring it to download from the URL field:
We should now get the following from the output of our download tool:
The next step is to parse this out using a JSON parse tool, configuring it to split out the DownloadData field
We can see the data has been parsed below, at the moment we are limited to 100 characters in this output as indicated by the data limit, however there are 1493 characters in total! So we will need to amend our workflow to be able to access all 1493 characters. Also notice that we want data starting from the data.results section of the JSON_name field. All of the data before that is metadata that we aren't too bothered about in the final output, but we will need to manipulate the data.offset value in order to get more than 100 characters.
At this point we can branch off our workflow with a filter:
Clicking on the false branch from our filter we can use a summarize with the following configuration:
Next a filter with the following configuration
Then a select tool can be used to drop the offset column before inserting a macro output for this part of the work flow and naming it "Iterative" with the abbreviation "I".
We are now going to go back to the filter from the earlier part of the workflow and focus on the top half before we branched off to do the bottom part:
A formula tool can be used to now remove the following text from the JSON_Name field
Inserting a text to columns tool at this point and setting the delimiter as a full stop will then give us a character identifier that we can use to change the data shape of the file with a cross tab tool
Note our new Row Id columns here:
I used a select tool to change the name of the Row ID2 field to "Headers" as these will become our field headers in the next step
Now we can crosstab to get our data in a more tableau friendly format:
There we have it! Some final cleaning before inserting a macro output at this stage also and calling the output name of the macro output "Data" with the abbreviation "D"
We then need to open the interface designer (from the view option in the top toolbar) in order to loop the bottom half of the workflow back around and iterate until we have all 1493 characters using the following configuration
Save the macro at this point and go back to our main workflow to insert it by right clicking and insert macro.
Run the flow and there we have it
1493 records have been output, 1 for each character!
