What is API? How to deal with the problem with Pagination API?

An application programming interface (API) is a way for two or more computer programs to communicate with each other. Some APIs will provide you with a link to let you collect all the data. In this situation, you need one workflow to finish extracting data. However, most APIs will give you a link with a page number to get a part of all data. If there are 10 pages in an API, do we need to duplicate our Alteryx workflow 10 times to get all data? No, we can use Macro in Alteryx to solve the problem.

 

What is Macro in Alteryx?

A macro is an Alteryx workflow or group of tools built into a single tool that can be inserted into another workflow. Create a macro to save an analytic process you perform repeatedly. Use the macro within a workflow without having to recreate the analytic process each time. In this blog, I’ll use Star War API as an example to show how to extract data from the Pagination API by using Iteration Macro.

Figure 1. The Micro of the Data Cleansing Tool

 

Steps to extract data from Pagination API

a) Download, Parse, and Analyze the first page of the data

URL: https://swapi.dev/api/people

Add the above URL in the TEXT INPUT tool.

Use the DOWNLOAD tool to download the data from the URL.

Parse the data with JSON PARSE.

Figure 2. Download & Parse Data

 

From the below screenshot, we can guess that there are 82 people in this dataset. The next page URL is in the JSON_NAME’s next.

Figure 3. Analyze the first page data

 

There are 10 people’s data on the first page data. SinceĀ  82 / 10 = 8.2, we can guess there are 9 pages in total. To prove it, we can create another workflow.

Figure 4. Estimate the total pages

 

b) Download, Parse, and Analyze the last page data

URL: https://swapi.dev/api/people/?page=9

Add the above URL in the TEXT INPUT tool and do the same thing as step 1 to check the output data.

Figure 5. Download and parse the last page data

 

The last page of data proves my hypothesis. There are 82 people in this API. On the last page, the next is NULL, we can use this as a stop condition.

Figure 6. Analyze the last page data

 

c) Split the streams for Iteration

Figure 7. Split the streams for Iteration

 

Figure 8. The data to be processed in the upper stream

 

Figure 9. The data to be processed in the lower stream

 

d) Data Wrangling for the people’s data

Apply the TEXT TO COLUMNS tool to split the JSON_NAME

Figure 10. Split the JSON_NAME

 

CROSS TAB the data to make the people in the row level.

Figure 11. Pivot the data

 

Figure 12. The result of the CROSS TAB

 

e) Prepare Data for the Next Iteration

Drag and Drop a FILTER tool on the canvas and add the stop condition.

Figure 13. Add stop condition

 

To prepare the data for the next iteration, we should let the output data’s format same as the input one.

Select the next page data and change the column name.

Figure 14. The consistency between input data and iteration output

 

f) Add Micro Output and Micro Input

Add MICRO OUTPUT into the last part of two streams.

Right-Click the TEXT INPUT and Covert it into the MICRO INPUT.

Figure 15. Add Micro Output and Micro Input

 

g) Micro Setting

Go to the Workflow Configuration and edit the Macro like the below screenshot.

Figure 16. Workflow Setting

 

Go to the Interface Designer and edit the Iteration like the below screenshot.

Figure 17. Interface Designer Setting

 

h) Run the Micro

Save the Micro file.

Open a new Workflow and add the first page data into the TEXT INUT tool.

Right-click the canvas and select the created Micro.

Connect them together and run the workflow to get all data.

Figure 18. Run the Micro

 

If there are any problems, please feel free to point them out. Besides, you can reach out to me onĀ LinkedIn. I will try my best to answer your questions about Tableau or Alteryx.

 

Joe Chan
Author: Joe Chan

Joe has an IT background with a master's degree in UNSW, majoring in AI and Data Science. During his studies, he realized Data is one of the most valuable assets a business can have and potentially has a tremendous impact on its long-term success. After graduation, his desire to level up his data analytics skills led him to join The Data School. He is interested in Data Wrangling, Data Visualization, and Machine Learning, eager to be a great Data Analyst to help businesses grow.