Preppin' Data

Challenge by: Jenny Martin Being an airline, Prep Air has to work closely with airports to ensure its passengers get the best experience possible. One airport in particular has been accused of driving passengers crazy by changing the gate allocations of flights multiple times before boarding occurs. Prep Air has discovered that this is due to the airport using a random number generator to assign gates for flights and manually correcting the errors in real time. Prep Air have been given the opportunity to demonstrate that they have a better way of allocating gates for the airport's busiest time of day. Whilst the stand numbers will still be set by the airport, we can at least ensure the corresponding gates are allocated in a more logical way.  The diagram below should help illustrate which stands can be accessed by which gates. The remote stands (10-12) can be accessed by any gate via a bus. However, passengers don't enjoy the bus rides, so we should try to minimise the time tha...

  Solution by Tom Prowse and you can download our complete workflow here .  This week we continued expanding our Prep Air use cases by looking into flight delays from some key airports, and also combined this with an interesting structure proposed by Michael .  Step 1 - Flag Airports The first step this week is to identify which of our rows is an airport. Due to the table structure, we can see that all of the rows with a Null in the Airport field are either a Type or Delay. Therefore, we can use the following calculation to flag the airports: Row Number IF ISNULL([Airport]) THEN 0 ELSE 1 END Step 2 - Fill Down Row Category Now we have flagged which rows are Airports, we want to use this and fill-down the below rows to identify which category each row is in.  The first step is to create a self-join on the Record ID where the RecordID >= RecordID. So we need to create a new Step, then join the new step to our Flag Airports step.  The join setup looks like this:...

Challenge by: Jenny Martin Prep Air want to do some analysis of flight delays to and from its key destinations. After many discussions with the airport, they finally agreed to share this data. However, it's not in the best structure, so we'll definitely need to do some prep before our analysis can begin. (It's almost like they're afraid of what we'll find!) A special thank you to Michael  this week for sharing a similarly structured dataset with us that sparked the idea for this challenge! Inputs We have 2 inputs this week: Information on the delayed flights, separated across multiple lines Aggregated view of flights which were not delayed Requirements Input the data Aggregate the data so that you have 1 row per flight delay, instead of the current 3 rows Make sure all Airport codes are valid. Group those which are not. Calculate the total delay and number of delayed flights for each Airport, for each journey type Combine with information on flights which were not d...

Solution by Tom Prowse and you can download the full workflow here .  This week we looked into incidents that have occurred in the aviation industry. The aim of the task was to parse some key information, and then count how many times each of our categories have happened.  There were plenty of different ways of completing this week's challenge, and make sure you check out Mo Hassn's solution where he challenged himself not to use Regex or Tableau Prep's native grouping feature.  Step 1 - Extract Information First up we want to extract the key information about the Aircraft, Location and Date from each of the strings. There are various ways to do this including RegEx or Splits, and we have decided to use RegEx to parse out the information. If you aren't familiar with RegEx then I would recommend taking a look at Regex101.com which will help you along the way.  Aircraft REGEXP_EXTRACT([Incident],'(^.*?)\s+(at|near)') Using this calculation, we are extracting any...

 Challenge by Tom Prowse. At Prep Air, we have decided to do some research into the risks of running an airline. We want to complete some analysis on some historic aviation incident reports so we can try to identify potential areas where we can make our airline safer. We have taken a selection of reports from the AeroInside  website, who document various incident reports from around the world. Each report contains information about the incident, but is a free text field so doesn't really have a structure. In this challenge, we want to parse out the key information from the string, and then see how many incidents occur that are related to our key categories.  Inputs Incident List Category List Requirements Input the Data Parse out the following information from the incident string:  Aircraft - eg, American B738 Location - Amsterdam Date - Apr 21st 2016 Incident Description - details about the incident Convert date field from string to a date Combine similar incident t...

Solution by Tom Prowse and you can download the workflow here .  This week we launched our new business: Prep Air. This is our airline that will fly between some of the world's major data hubs and other holiday destinations. For the challenge this week we looked at the first quarters revenue and then try to determine what the rest of the year would have looked like if the world hadn't stopped.  Step 1 - Calculate Sales, Targets, & Variance The first step this week is to combine the Ticket Sales & Sales Target tables. We can do this by joining the two together with the following join conditions:  We can then rename our value fields so that they match the correct values for Sales & Target. Then use both of these fields to calculate the Variance to Target:  [Sales Value]-[Target Value] Our table should now look like this:  Step 2 - Clean Projections Table Next, we want to turn our focus onto the 2020 Projections table and make some changes so that it ca...

 Challenge created by: Carl Allchin For those who have taken part in the Preppin' Data for a while you know we love Chin & Beard Suds Co to provide some 'fun' data challenges. Well we are excited to announce the take-off of our new business: Prep Air  is our airline that will fly between some of the World's major data hubs as well as when the Dr Preppers basically want to take a well earned holiday. We saw an opportunity to enter the market whilst air travel struggles but we believe our airline that is based on great data-driven insights is a fantastic way to go (plus as the CEO is over 6 foot tall the legroom is fantastic even in economy class!).  This Week's challenge Clearly this hasn't been the best year to start an airline. Our Chief Financial Officer (Jenny) likes to remind me of that everyday but we've started now so this week's challenge will help me persuade her that there is a future for our fledgling airline. I want to take the first 3 mon...