BYU Student Author: @Kyle_Nilsen, @Parker_Sherwood
Reviewers: @Hyrum, @Mike_Paulsin, @Sterling_Lane
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
The year is 2012 and you are in the market for a new car! You know you want a reasonably fuel efficient car within your price range. Specifically, you’re looking for a car with at least 28 MPG and under $17,000. You downloaded some car sales data for 2008-2012, and it’s time to do some analysis. Using Alteryx and the power of its preparation tools, you’ll be able to make an informed decision. All of your work will be done in Alteryx using the data from the“Car_sales” worksheet in the provided Challenge115_Data file. The other file below describes the columns in the “Car_sales” worksheet.
Instructions
Complete the following:
-
For the “Fuel Efficient (T/F)” column, use a logical function (<, >, <=, >=) to identify whether a car gets at least 28 MPG. The formula should return “TRUE” or “FALSE”
-
For the “Fuel Efficient (Desc)” column, use an IF function to identify whether a car gets at least 28 MPG. If a car gets less than 28 MPG, it should be labeled as “Inefficient”. Otherwise, the car should be labeled “Efficient”.
-
For the “Is it affordable?” column, use an IF function to identify whether a car is within your budget of $17,000. If it is, return the word “Affordable”. Otherwise, create a label to show that the car isn’t affordable.
-
For the “Fuel Efficient and within budget” column, use an IF function to test if a car is both “Efficient” and “Affordable”. If a car is both efficient and affordable, it is a great choice. If it’s not both efficient and affordable, you’ll need to think about it. Your function should return something along those lines.
-
As you’ve done a little more research, you’ve realized you want to lower your budget to $16,000. For the “Price range” column, you’ll create categories for the prices of cars that are more descriptive than “Affordable” or not. Specifically, use the IF and ELSEIF formula and the following chart to create your function.
-
In the “How Fuel Efficient?” column, use IF AND ELSEIF statements to create more specific categories for fuel efficiency. Use the following chart as a guide.
-
In the “Further analysis” column, use the IF or ELSEIF functions to conduct some further analysis of your own! What would you want to consider if you were buying a car?
-
Add filters to the data. Filter on the “Fuel Efficient and within budget” column to find the “great choice” cars. Sort by the “Price range” column so that the most affordable of the “great choice” cars are at the top of the list.
-
Add a filter based on the “Further analysis” column to select from the remaining cars that fit your own preferences. Use the select tool to choose only the manufacturer and model. Finish off with a browse tool to view your data.
Data Files
Suggestions and Hints
- You should be able to do almost every step using either a formula or filter tool. You can find these in the preparation tab.
- Alteryx will sometimes default to a data type that is incorrect for a column’s values. This will make you unable to use certain formulas, i.e., returning “affordable” for the “Is it affordable?” column (which defaults as a double data type). Make sure to change the data types of all the initially null columns using a select tool to match their expected output. This will be a Bool type for “Fuel Efficient (T/F) and V_WString for the rest.
- You can use a Data Cleansing tool after loading in the data to replace null values with 0 or a blank string. This will help you keep data consistent. For example, step 3 asks you to return “Affordable” if the car is below your budget of $17,000. You should not consider a car without a price as affordable. Make use of the data cleansing and be mindful of what values were replaced and what the new values signify.
- For step 4, think about the columns you have already created to make this more concise.
- Having an output on your finished dataset is common practice. Include the browse tool to view your results and keep up with this standard.
Solution
