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.
- Since all indexes increased from 1995 to 2018, we could say USA tourism developed rapidly.
- 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.