151|ALTERYX – Shipping for Samuel

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:

  1. Only orders that have a Customer PO
  2. Only embroidery orders (2)
  3. Only orders where the order date is in 2011
  4. Only orders that have Samuel Clarkson as the sales rep (4)
  5. Only orders that were shipped out using “UPS” (2)

Additionally, you should ensure your report is formatted using the following guidelines:

  1. 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)
  2. Only keep the first 50% of the rows
  3. Headers should appear as below
    Picture2

Instructions

  1. Download the challenge data and data dictionary below
  2. Familiarize yourself with the data and import the data into Alteryx using an Input tool
  3. 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
  4. Use a select tool to limit the number of columns that are displayed
  5. Use a sort tool to order the information as specified above
  6. 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

This is a great challenge to help beginners to understand the power of the formula tool! The sample tool seems like a good tool to know if you are planning on doing audit.

This challenge really got me thinking about how dynamic the filter tool is. I’d also never used the ‘First N%’ option on the Sample tool before. Interesting! Great beginner challenge.