BYU Student Author: @Kyle_Nilsen
Reviewers: @Carter_Lee, @Dallin_Gardner
Estimated Time to Solve: 60 Minutes
We provide the solution to this challenge using:
- Alteryx
Need a program? Click here.
Overview
Welcome to the international world. You have been hired by a firm that does international business in Brazil. Your department is in charge of calculating the sales taxes for these transactions based on the states in which your Brazilian clients live. Unfortunately, the data is flawed; there is no state data in the sales records, and the map your company has used is based on the state lines from 1920. Since then, the country’s federal district was relocated from Rio to Goiás and five new states have been created. With all of these changes, you doubt the $33,528.185 sales tax value currently reported is correct.
Your task is to update the map stored in your company’s database with the new states, then find the state where each transaction occurred. Afterwards, calculate the total sales tax value that will need to be paid (note: all numbers in the data are fictional and do not represent anything outside the context of this challenge).
Instructions
- Download all of the files attached at the bottom of this challenge. Make sure all are put in the same folder for convenience.
a. The Challenge233_International_Inquiries.yxmd file is where you will build the workflow. Within the file there is a text input tool that has data in it for the changes (it looks like a input data tool with three dots on the side). Take a moment to analyze and understand the data contained in this tool.
b. These files are necessary for completion of the challenge: Brazil_1920.yxdb, Missing_States.yxdb, BrazilTaxes.csv, Challenge233_Sales_Data.csv
c. The Brazil_Present.yxdb file is included to help visualize what the present-day map should look like, but it not necessarily required for completing the challenge. - Fix the input data paths within the workflow to match where these files are saved.
- Map the change of the Distrito Federal from Rio de Janeiro to Brasilia (Hints listed below)
- Join Missing_States data with the text input by the “New State Gaining Land” and combine it with the data from the previous step.
a. [details= “Hint 1”] The first join will combine the missing states with the new state gaining land, giving you the fields “1920 State Losing Area”, “New State Gaining Land,” “nome,” and “SpatialObj.” The second join will add the moved federal district, and should remove the “New State Gaining Land” column for simplicity moving forward. [/details] - Remove the borders of the new land from the old land with a Spatial Process tool
a. [details= “Hint 1”] Cut the new states from the old states with this tool. I renamed my new state spatial object as “New_SpatialObj” to keep it clear. [/details] - Use a summarize tool to map the new boundaries
a. [details= “Hint 1”] Aggregate the “1920 State Losing Area” with a Group Byand rename it to “nome” (Portuguese for “name,” signifying the state), and then aggregate SpatailObj_Process by Create Intersection. [/details] - Unify the new states and old state boundaries to produce the updated map. (Hint Below)
- Use a Create Points tool on the Challenge233_Sales_Data to create coordinates on the map. Then use a Spatial Match tool to find which states correspond with these coordinates.
- Join the BrazilTaxes data with the prior step to match the states with their sales taxes. Then calculate the sales tax values by multiplying the tax value by .01 and the sales. Summarize this column to get a total sales tax value.
Data Files
- Challenge233_International_Inquiries.yxmd
- Challenge233_Sales_Data.csv
- BrazilTaxes.csv
- Brazil_1920.yxdb
- Missing_States.yxdb
- Brazil_Present.yxdb
Hint 3.1
Use a filter to pull Rio and Distrito Federal from the 1920 data.
“Hint 3.2”
Use a summarize tool to aggregate the true output state (nome) by “Last” and the SpatialObj by “Combine.” Then Union this output with the false output from the filter.
“Hint 7.1”
Use a union tool to merge the data from the last step and the unmatched records from step 4.
Solution
Challenge233_Solution.yxmd
Solution Video: Challenge 233|ALTERYX – International Inquiries
[details="Solution Images’]
[/details]