BYU Student Author: @Marta_Ellsworth
Reviewers: @Andrew_Wilson, @Sterling_Lane
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
Welcome to the Simple Screenprinting team! Our screenprinting company is facing a challenge with our job tracking system. We’ve got data issues that are slowing us down, and we need your help to fix them. Your task is to clean and organize our job data using Alteryx. But here’s the catch: you can only use the Formula tool, plus inputs and outputs, to get the job done.
This is a great chance to show off your Alteryx skills and make a real difference in how we operate. You’ll be tackling things like missing information, messy formats, and figuring out job priorities. It’s all about making our data cleaner and our processes smoother. Let’s jump into the data and start making improvements. With your help, we can keep our operations running smoothly and our customers happy!
Instructions
- Clean Missing Data:
- Import the Orders tab from the starting dataset.
- Use the Formula tool to fill missing values in the SalesRepID column with a placeholder value of 0. This will indicate jobs without an assigned sales representative.
- For the OrderDate, DueDate, and ShipDate columns, fill missing dates with a specific date that indicates an unknown date (e.g., 1900-01-01). This helps in identifying jobs with missing date information. (Extra Challenge: Try and do everything in question 1 in a singular formula tool.)
- Format Job Names:
- Some job names contain extra quotation marks (e.g., “DON’T STOP ME NOW”). Use the Formula tool to remove these quotation marks from the JobName column.
- Calculate Days Since Shipment:
- Use the Formula tool to calculate the number of days since shipment. Use 2021-08-12 as today’s date. Create a new column named DaysSinceShip for this purpose.
- Categorize Job Types:
- Use the Formula tool to create a new column named JobTypeCategory based on JobTypeID. For example, if JobTypeID = 2, then JobTypeCategory = ‘Embroidery’, if JobTypeID = 7, then JobTypeCategory = ‘Art Only’, and so on. (You can find this information on the JobTypes Table.)
- Total Charges:
- Use the Formula tool to create a new column named TotalCharge that sums the charges in the ChargeArt, Setup_Charge, and Charge_Special columns.
- In the same formula tool change the data type for the TotalCharge column to a float to allow for possible decimals.
Data Files
Suggestions and Hints
- Remember you are only using the formula tool, so you don’t need to join the JobType table, just use the information to create an IF statement.
- Make sure you format the dates as YYYY-MM-DD in the formula tool.
- If you aren’t sure what function to use you can click the ƒx button in the Formula tool and Alteryx will give you options based on what you are trying to accomplish.
- Use the function DateTimeDiff() for question 3.
Solution
