143|ALTERYX – Corporate Audit Preparation

BYU Student Author: @Sterling_Lane
Reviewers: @Andrew_Wilson, @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
As the current Regional Manager at the Simple Screenprinting branch in Orem Utah, you are becoming concerned about an impending corporate audit that specifically targets your sales team. While acknowledging their exceptional performance, corporate seems to also suspect the figures may be overly optimistic. How dare they! As such, they are requesting that you review the recorded sales from 2010 to 2020 and ensure they align appropriately with the volume of products dispatched. You need to ensure the sales data is accurate and complete for corporate so they don’t find any wrongdoing at your branch.

Instructions

  1. Download the Starting Data Alteryx Package. This is where you’ll want to build your solution as the input data tools are already loaded in for you to start your work from.
  2. Your top salesman provided you a list of items which names have been updated which is contained in the Item Name Replacements Text Input file. Use a Find and Replace tool to update the names in the Item Table with the names in the Item Name Replacements table.
  3. Which items were not sold at all between 2010 and 2020? Use the output tool to output an excel spreadsheet of item names you can send to Corporate with these items so you can ask them if we should carry this inventory anymore.
    • Use a Join tool to combine the ‘Orders’ and ‘OrderDetail’ sheets together. We only want to consider orders which have the OrderDetail filled out.
    • Use another Join tool to combine the output from above to the updated Items table output to get the item information for each order. We are focusing on items only, so restrict your output to only include the three columns from the Items table.
    • For all items not present in the Order Table, use an Append Fields tool to append the relevant ReportStatus to the table. For all items present in the Order Table, use the Append Fields tool to append the relevant ReportStatus to the table. You can find the text input files with these statuses preloaded on your starting workbook.
    • Use the Union tool to combine both outputs (one with the items not in the ordered items and one with the items in the ordered items)
    • Finally, let’s clean our output a little bit. Use the Unique tool to filter out all duplicate SKUs. Use a Filter tool on the ReportStatus column to isolate only the items you want to ask corporate about. Finally, use a select tool to eliminate the Report Status column. You now have a clean report you can send to Corporate!

Data Files

Suggestions and Hints
  • If you highlight both the OrderDetail and Order tables, right click, and select ‘Cache and Run Workflow’, your process times should speed up dramatically. This is a good way to ensure time isn’t wasted loading in large sets of input data each time you run your workflow.
  • When using the Append Fields tool, ensure the ‘Source’ is the item you want to append and the ‘Target’ is the dataset you want to append the item to.
  • When using the Find and Replace tool, ensure the field you want to replace or append to a dataset is on the ‘Replace’ arm of the tool and your future output is on the ‘Find’ arm of the tool.

Solution


This was my solution. I thought that the append and joining functions were especially helpful and it was a good reminder that there are some Alteryx data cleaning functions that cannot be done in excel