An API (Application Programming Interface) allows users to access data from web systems or online sources by sending requests and receiving structured responses.
This process entails collecting information from a website through an API call. The purpose of making an API call is to allow users to collect information from a website to perform analysis, reporting or building dashboards.
Some API's require an API key for access (a password), while others are open. However, even open APIs track usage via your IP address, meaning if multiple users request the same IP, you may hit rate limits or be temporarily blocked.
Note: API keys are sensitive credentials and should never be shared with anyone. If they are used without permission, they can be used to access your account or data.
In this guide, I'll walk through how to make requests within Alteryx using the Ricky & Morty API website to extract information about episodes. The documentation is a valuable guide to follow to make this process easier: https://rickandmortyapi.com/api
Step 1: Text Input
We'll be requesting information about the episodes. We begin by inserting the URL using the text input.
Step 2: Download Data
Then use the download tool to make the request.
Step 3: JSON Parse
Next, you will need to use the JSON Parse tool.
Step 4: Generate Rows
Now we need to extract all the information from the pages. To do so, we must filter our data to display only the page number within our request. We do this because Alteryx will only display a certain number of pages unless we specify to extract all the information concerning the episodes we need to filter. Then change this to an integer and use the generate rows tool.
Step 5: Formula
Then, we need to use the select tool and convert the pages to a V_WString for our calculation in the next step. Now, we need to create a formula to join the URL with text from the website's documentation page, along with the page number information we made in the previous step. This will be the new URL. This calculation will allow us to grab all the information from all the pages.
Step 6: Download & JSON Parse
Then use the Download tool (select the new URL) followed by the JSON Parse tool to extract all the episodes from Rick & Morty.
Now that we have all the rows, we need to filter out the information we do not need. Our aim is to have the ID, name, episode, and air date. To achieve this, we need two filters. First, we need to filter out rows that contain 'info' and then filter out 'characters'.
Step 7: Text to Columns
Next, we need to parse, using the text to columns, as we want our information split.
Step 8: Cross Tab
Now, we need our rows to become columns and the JSON_ValueString2 column to become the values for those columns. To do this, we will transform our data using the crosstab tool.
It's important to group the data on specific fields, as we want all the information per episode. If we don't group, then our results will not be as intended. In this case, we are grouping pages and episode ID, and we want the JSON_Name22 to become the column headers and JSON_ValueString2 to become the values.
Step 9: Select & Sort
Now, all that is left to do is use the select tool to remove and move fields to the appropriate position, and use the sort tool to order by ID. Then, you have your final output of all the information on episodes.
