Preppin' Data

Challenge by: Jenny Martin It's the final week of beginner month and we're going to spend a little more time diving deeper into joins, calculations and outputs.  Prep Air are interested in creating a workflow that has multiple outputs depending on user requirements. They want users to be able to answer the following questions: What are the details of the customers who have booked flights and which routes are they travelling on? Which customers are yet to book a flight in 2024? Which flights are yet to be booked by customers in 2024? The datasets you'll be working with are fairly large so you'll need to decide which tables to join (and when) to be as efficient as possible. You may wish to use this as an opportunity to explore the sampling options in Tableau Prep too! Inputs There are 3 tables to connect to for this challenge: Prep Air Ticket Sales Prep Air 2024 Flights Prep Air Customers Requirements Input the data For the first output: Create a dataset that gives all th...

Solution by Tom Prowse and you can download the workflow here . This week we are going to focus on the join functionality and how we can use the conditions so that they act as a filter within our analysis. Again, this is adding to our fundamental knowledge bank of examples that are key to getting started with data preparation. Step 1 - Input Flow Card Data First we want to input all of our data related to whether someone has a flow card or not. To do this we can input one of the sheets that includes the flow card information. From here we can create a wildcard union within the input step so that we include the 3 sheets containing information about the flow cards.  To do this we need to set a worksheet filter so that we only include the relevant sheets (exclude the Seat Plan). Within the worksheet filter we can set it to include sheets where the pattern matches "*Card*". By including the * this acts as a wildcard and will include any other characters before or after the word C...

 Created by: Carl Allchin Last week you needed to use a Join technique to pair the flight data with the sales targets. This week you'll be using Joins again but this time in a different way.  When using Joins, there are two things you need to set up: Join Condition - what logic will join similar rows of data together from each data set Join Type - determines what data you will bring back based on the Join Condition This challenge will test using join types to return the data you require for the output. This week we are trying to understand which seats aren't chosen on our planes as we're thinking of applying fees for customers to choose their seat when booking.  Input Seat allocation per customer. We have had three downloads sent to us, one for the Flow Card customers and two where they aren't Flow Card customers. They're available here . The seating plan for our planes Requirements Input the Excel workbook containing the four worksheets of data Union the Flow Card...

Solution by Tom Prowse and you can download the workflow here .  This week we continued our analysis with the Prep Air flow card by introducing target data into our flow.  Step 1 - Input Data First we need to input our data. This is in a few different files so we need to make sure we have connected to all three sources from the given input files. Once we have connected we can bring the outputs from Week 1 into the workflow - this is two data sources containing information about Flow / Non-flow card holders. These can be combined into a single table by using a union step.  The flow/non-flow card input should look like this:  As our third input, we want to bring in the table containing the target information from Q1. As each quarter is separated into different tables, we can use the 'Union multiple tables' option in the input step to bring all of the tables in from this input.  This will union all 4 of the tables and out targets table should look like this:  ...

 Created by: Carl Allchin     The world is swimming in data and with so many sources everywhere, it's often on you to tie them together. Most data tools need to read from a single data so will use Unions, Joins and Logical Relationship models to tie them together. In this week's challenge we will introduce you to joining data sets together to prepare them for analysis.  This week's challenge is to link together a Quarterly Sales Target data source (an Excel Workbook) with our original sales data (Week One output). Is Prep Air meeting its targets? Input Week One's output files and Week Three's new input :  In the Excel file this week there are four tables of data, one for each quarter this year. Requirements Input the outputs from 2024 Week 1 challenge Input the new targets Excel sheet (Q1 - 4)  Correct the Classes being incorrect as per last week Economy to First First Class to Economy Business Class to Premium Premium Economy to Business Find the First Le...

Solution by Tom Prowse and you can download the workflow here . We continued our analysis from the 2024: Week 1 challenge where our output from there has become the input for this week. This week we are looking at unions, aggregations, and reshaping our data. Step 1 - Union inputs Our first task this week is to input both of the input tables. We'll need to add a new connection for both of these and then bring them in as separate input steps. Once they are added to the workflow we can drag one just below the other so that the union box is highlighted, and therefore creating a union step. When we union data, this 'stacks' the tables on top of each other. This works in this use case as there is a similar field structure in both tables so we want to keep the fields the same and make the table longer. Step 2 - Convert Date  Next we want to convert the date field so that we can identify which Quarter it is from. Using the in-built functionality within Tableau Prep, this is nice a...

Created by: Carl Allchin It's the second week of our introductory challenges. This week the challenge will involve unions, aggregation and reshaping data. Input The input data set for this week is the output from week one. You can download them here . Requirements Input the two csv files Union the files together Convert the Date field to a Quarter Number instead Name this field Quarter Aggregate the data in the following ways: Median price per Quarter, Flow Card? and Class Minimum price per Quarter, Flow Card? and Class Maximum price per Quarter, Flow Card? and Class Create three separate flows where you have only one of the aggregated measures in each.  One for the minimum price One for the median price One for the maximum price Now pivot the data to have a column per class for each quarter and whether the passenger had a flow card or not Union these flows back together What's this you see??? Economy is the most expensive seats and first class is the cheapest? When you go and ...

Solution by Tom Prowse and you can download the workflow here . This is the first challenge of 2024 and we are running a beginner month once again. These challenges will focus on getting started with Tableau Prep (or data prep in general) and help you start building those good habits to achieve your New Year's resolutions! Let's see how we can solve this challenge. Step 1 - Input data As with all these challenges we need to start with inputting the source data into our workflow. This can be downloaded from the challenge requirements and then input into the workflow.  We will be working with a CSV so we need to choose a text file and then make sure the table has been brought into our workflow canvas as an input step. Once we have the input, we can create a new clean step by pressing on the + to the right on the input Step 2 - Split Flight Details Now we have the data into our workflow we can start to clean it up and create our outputs.  First, we want to split the Flight Detail...