BYU Student Author: @Andrew_Wilson
Reviewers: @Sterling_Lane, @Marta_Ellsworth
Estimated Time to Solve: 30 Minutes
This is an intro challenge that is part of the Alteryx Learning Path.
We provide the solution to this challenge using:
- Alteryx
Need a program? Click here.
Overview
At Simple Screenprinting we believe in quality work and accurate information. Your manager is preparing for a meeting to discuss the effects of COVID-19 on sales here at Simple Screenprinting; however, because the Orders table is part of a normalized database, it is hard to read without always referencing other tables. As you know, several of the columns (i.e. FreightID, SalesRepID, and JobTypeID) are presented foreign keys in other tables. Because of the upcoming meeting, your manager needs the information to be summarized and readable to other managers. It is extremely important that the data be stored in an Excel spreadsheet because your manager has never used Alteryx and thus cannot access an Alteryx file.
You will produce two separate reports, one for 2019 and one for 2020 outlining the number of each JobTypeID per Sales rep based upon how it was shipped out. You will use the Find and Replace, Transpose, Cross Tab and Output Data Tools to create readable reports for your manager’s upcoming meeting.
Your report should include the following information:
- Orders that were placed in 2020 based OrderDate
- Sales Rep Name
- Job Type name
- Count of each relevant shipping type, excluding delivery by Sales Reps (i.e. SHC – Deliver), in separate columns
- The Year (2020) in a separate column
Additionally, you should ensure your report is formatted using the following guidelines:
- Orders should appear based on 1.) the Sales Rep from A-Z, 2.) Job Type from A-Z
- Headers should appear as below
Instructions
- Download the challenge data and data dictionary below
- Familiarize yourself with the data and import the data into Alteryx using an Input tool (Orders table, Job Types Table, Employees Table, and Freight Table)
- Change relevant data types (JobTypeID, SalesRepID, FreightID) to allow use of the Find and Replace tool (See hint 1)
- Filter out any orders whose OrderDate is not in 2020
- Use the Find and Replace tool to change data in the orders table (See hint 2)
- SalesRepID to Sales Rep Name
- Job Type ID to Job Type Name
- FreightID to Freight Name
- Use a Cross Tab tool to rearrange the data and count the quantity of orders based on Freight type. Make sure the JobTypeID and SalesRepID are selected in the Group By wizard. The column headers should be FreightID and the Value should be a count of the JT_Num (See Hint 3)
- Use the Append Tool to add the year (2020) to each line of data (See Hint 3)
- Select only the necessary rows
- Order by SalesRep and JobType
- Output the information to an Excel document using the Output tool. The sheet name should be the year (2020) you are analyzing
- Repeat the whole process for 2019 and compare the outputs. See if you can see any significant difference between the two years. You can compare more frequent shipping methods (i.e. Will_Call) to see if there is a significant difference in number of orders placed
Data Files
Suggestions and Hints
- The Find and Replace tool can only work with string data fields. You need to use a Select tool to change the data type before using a Find and Replace tool.
- Be careful that you connect smaller tables (JobTypes, Employees, Freight) to the Replace “R” side of the tool and the main table (Orders) to the Find “F” side of the tool. For more information, please refer back to the Alteryx Getting Started Module
- The Cross Tab tool is designed to use numeric data. Use a select tool to change the JT_Num to a Double.
- Use a Text Input tool to manually create the desired text input (2020)
- In the Output tool, under Output options select “Overwrite Sheet or Range” to prevent Alteryx from creating multiple files or sheets
- To repeat work you have already done, try copying and pasting
Solution
