For the first week’s training on Alteryx, we were given an Alteryx Weekly Challenge and were required to finish it and present it within 4 hours. If we had additional time, we could create a simple dashboard by Tableau and dig out some interesting insights into the output data. I was selected to deal with challenge #7. You can go to the below website for the challenge.

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-7-Download-Data-and-Parse-JSON/td-p/36734

 

Introduction to the challenge

For challenge 7, you should download data with an API and parse that data from JSON into a usable format. The user is trying to get aggregated Annual Inbound Tourism Statistics for the USA dating back to 1995. The Text Input contains the URL for the API request.

Figure 1. Input Data

 

Figure 2. Desired output data

 

Workflow to the Alteryx challenge

a) Download JSON from the URL and Parse it

Firstly, apply the TEXT INPUT tool and paste the URL on it.

Secondly,  use the DOWNLOAD tool to download the JSON from the URL.

Thirdly, drag the JSON PARSE tool to canvas and use it to parse the JSON.

Figure 2. Workflow part1 – Download & Parse JSON

 

After parsing the JSON, we can see JSON_Name is the desired column name, and JSON_ValueString is the desired data.

Since the depth of the JSON hierarchy is 4, we can use TEXT TO COLUMNS tools and input 4 to split JSON_Name.

Figure 3. After JSON Parsing

 

Figure 4. TEXT TO COLUMNS configuration

 

b) Data Wrangling

After splitting the column, we can see we need column names, data, and unrelated information about the dataset stacked together. Therefore, we can filter out the unrelated data and then process the column names and data.

Figure 5. After splitting

 

For the column names, you can apply FILTER to remove the unrelated data and use SELECT to maintain the fields of column names and their values. Lastly, employ the CROSS TAB tool to turn it into one row. (Preparation for Union)

For the data, do similar jobs.

Figure 6. Workflow part2 – Wrangle Column Names & Data

 

Figure 7. CROSS TAB configuration – Column Names

 

Figure 8. Output row of column names

 

Figure 9. CROSS TAB configuration – Data

 

Figure 10. Output Data

 

c) Union Column names and data

Firstly, union the output column names and data.

Secondly, apply DYNAMIC RENAME to make first-row data as field names.

Finally, use SELECT to select the desired fields (Inbound).

Figure 11. Workflow part3 – Union column names & data

 

d) Entire Workflow

Figure 12. Entire Workflow

 

Insights into the output data

After solving the question, I created a Dashboard and found some interesting insights into the output data.

  1. Since all indexes increased from 1995 to 2018, we could say USA tourism developed rapidly.
  2. Although more people depart the USA than arrive in the USA most of the time, travel spending in the USA was much higher than in other countries.

 

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.