*BYU Student Author*: @Boston

*Reviewers*: @Spencer, @Christian

*Estimated Time to Solve:* 45 Minutes

We provide the solution to this challenge using:

- Excel
- Alteryx

Need a program? Click here.

**Overview**

You have been newly hired as an intern at Rango Incorporated and your boss has approached you with an important task. Your resume boasts adept skills in Excel and Alteryx, so your boss thought you would be perfect to help go through data to answer important questions. However, there are a few problems with the dataset. Within the excel workbook, there are three sheets related to Employee, Trip, and Purpose data. The person who input the employee data used unprofessional capitalization when each row of employee data should appear like: “John Smith, Sales, 000, Miami, Florida.” In the Trip data, the same employee accidentally input the first 68 rows of data into column “H” and did not split up the data into their proper rows or columns. Your boss would like you to organize, upload, and analyze the data within Alteryx to answer three important questions:

- How many employees spent more than their estimated costs on trips?
- Is there a correlation between excess spending and the number of complaints for an employee?
- Which trip purpose had the greatest excess spending?

**Instructions**

- Parse the Trip data using Alteryx into the correct rows and columns.
- Clean the Employee data using Alteryx. Your boss does not want to use any rows that have any null or empty values. For example, you can exclude any row of data that has a null employeename, department, employeeid, city, state, or complaints. There are some duplicate employees in the Employee data that need to be combined and their complaints summed together.
- Join these tables so that you can do further calculations.
- Use Alteryx tools to find out if employees spent more than their estimated costs by creating a new column “difference” that calculates the difference between the average estimated and actual costs.
- For question 3, use all available data (regardless of difference amount) to find the total difference for each Purpose ID.
- You should finish with two final tables with the following headers:

**Data Files**

## Suggestions and Hints

The Left, Mid, Right functions or delimiter tool may be helpful to parse the Trip data into the correct columns. It may be helpful to remove the whitespace in the Trip data and unwanted columns with the data cleansing tool.

**Solution**

## Check Figures

The “employeename” table should have 9 employees/rows and the “ExpPurposeDesc” table should have 5 rows.

Challenge136_Solution.xlsx

Challenge136_Solution.yxmd

*Solution Video:* Challenge 136|ALTERYX – To Spend or Not to Spend