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. The date is January 15, 2012, and we are auditing last year’s sales information. Your boss has asked you to find out which of Samuel Clarkson’s (Sales Rep) orders had a customer PO and were shipped out using UPS. Your job is to review all sales by Samuel Clarkson in 2011 that have a Customer PO to see if there are any that were shipped out using UPS and prepare a report for your boss.
In this challenge, you will be demonstrating your understanding of the Filter, Select, Sort, and Sample tools. Since you recently began using Alteryx, you know that these tools can help you identify and pull only the information that you need (Orders table) in order to prepare the report for your manager.
Your report should include the following information:
- Only orders that have a Customer PO
- Only embroidery orders (2)
- Only orders where the order date is in 2011
- Only orders that have Samuel Clarkson as the sales rep (4)
- Only orders that were shipped out using “UPS” (2)
Additionally, you should ensure your report is formatted using the following guidelines:
- Order the results by Order Date starting with January and flowing to December. If two orders were placed on the same date, the Customer PO with the smaller PO number should be first (ascending)
- Only keep the first 50% of the rows
- 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
- Using a combination of filter tools eliminate all rows of data without a Customer PO, with an order date outside of 2011, that weren’t through Samuel Clarkson and orders that weren’t shipped using UPS
- Use a select tool to limit the number of columns that are displayed
- Use a sort tool to order the information as specified above
- Use the sample tool to keep the first 50% of the rows
Data Files
Suggestions and Hints
- A majority of the work in this challenge will be done using filter tools
- If the tool appears to have done nothing, make sure you are looking at the appropriate output (often it will default to showing the input, not the output)
- In the Sort tool, Ascending starts small and gets larger while Descending starts larger and gets smaller
- You can filter to a range of dates by selecting filter by “Range” → “Fixed”
- The columns JobType SalesRepID, and FreightID refer to other tables that can be found in the excel file
- The sample tool has options to allow you to select the first N rows or the first N% of rows. Select the N% of rows and then adjust the N= text box to get the results you are seeking.
Solution
