2021: Week 18 Prep Air Project Overruns

 Challenge by: Carl Allchin

In January we ran a month of challenges that focused on letting people try Preppin' for the first time without jumping into some of the more challenging functions. This month, we want to build on that knowledge with a set of beginner / intermediate challenges. We'll keep going with the help links but clearly flag up the focus of the challenge. 

If you haven't tried the January challenges but wonder if Preppin' might be too hard (it won't be), please go back and try these challenges. You will find these challenges and solution posts here.

The Challenge

This week's challenge is focused on Dates and the calculation functions available to you. Here's a recent blog post that I wrote that might help you if you want a little extra support.

This week we would like you to prepare you data for building a Gantt chart and supporting information on a dashboard (you don't have to build the dashboard but bonus points if you do!). Prep Air (our fake airline) has had a number of projects that have been over-running and the leadership team want to know why: 

The data starts in a state I've seen in a few systems. Preparing the data to make analysis easier is the aim this week. 

Input

The input file is an Excel file with a single tab (click on the link to go to the file)


Requirements

Here's what we're asking of you:
  • Input the data
  • Workout the 'Completed Date' by adding on how many days it took to complete each task from the Scheduled Date
  • Rename 'Completed In Days from Schedule Date' to 'Days Difference to Schedule'
  • Your workflow will likely branch into two at this point:
1. Pivot Task to become column headers with the Completed Date as the values in the column
  • You will need to remove some data fields to ensure the result of the pivot is a single row for each project, sub-project and owner combination. 
  • Calculate the difference between Scope to Build time
  • Calculate the difference between Build to Delivery time
  • Pivot the Build, Deliver and Scope column to re-create the 'Completed Dates' field and Task field
    • You will need to rename these
2. You don't need to do anything else to this second flow

Now you will need to:
  • Join Branch 1 and Branch 2 back together 
    • Hint: there are 3 join clauses for this join
  • Calculate which weekday each task got completed on as we want to know whether these are during the weekend or not for the dashboard
  • Clean up the data set to remove any fields that are not required.
  • Output as a csv file

Output


One file:
10 data fields:
  • Project 
  • Sub-Project
  • Owner
  • Scheduled Date
  • Completed Date
  • Completed Weekday
  • Task
  • Scope to Build Time
  • Build to Delivery Time
  • Days Difference to Schedule
18 rows of data (19 including headers) 

Here's our full output for comparison. Remember we don't care about the order of the columns or rows as Tableau Desktop will import them in whatever order they come in!

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help! 

Popular posts from this blog

2023: Week 1 The Data Source Bank

Image
 Created by: Carl Allchin Welcome to a New Year of Preppin' Data. These are weekly exercises to help you learn and develop data preparation skills. We publish the challenges on a Wednesday and share a solution the following Tuesday. You can take the challenges whenever you want and we love to see your solutions. With data preparation, there is never just one way to complete the tasks so sharing your solutions will help others learn too. Share on Twitter, LinkedIn, the Tableau Forums or wherever you want to too. Tag Jenny Martin, Tom Prowse or myself or just use the #PreppinData to share your solutions.  The challenges are designed for learning Tableau Prep but we have a broad community who complete the challenges in R, Python, SQL, DBT, EasyMorph and many other tools. We love seeing people learn new tools so feel free to use whatever tools you want to complete the challenges.  A New Year means we start afresh so January's challenges will be focused on beginners. We will use dif
Read more

2023: Week 2 - International Bank Account Numbers

Image
Challenge By: Jenny Martin For week 2 of our beginner month, Data Source Bank has a requirement to construct International Bank Account Numbers (IBANs), even for Transactions taking place in the UK. We have all the information in separate fields, we just need to put it altogether in the following order: Inputs A list of the transactions, with information about the receiving bank account  A lookup table for the SWIFT Bank Codes  Requirements Input the data In the Transactions table, there is a Sort Code field which contains dashes. We need to remove these so just have a 6 digit string ( hint ) Use the SWIFT Bank Code lookup table to bring in additional information about the SWIFT code and Check Digits of the receiving bank account ( hint ) Add a field for the Country Code ( hint ) Hint: all these transactions take place in the UK so the Country Code should be GB Create the IBAN as above ( hint ) Hint: watch out for trying to combine sting fields with numeric fields - check data types Re
Read more

How to...Handle Free Text

Image
Free isn't always a good thing. In data, Free text is the example to state when proving that statements correct. However, lots of benefit can be gained from understanding data that has been entered in Free Text fields. What do we mean by Free Text? Free Text is the string based data that comes from allowing people to type answers in to systems and forms. The resulting data is normally stored within one column, with one answer per cell. As Free Text means the answer could be anything, this is what you get - absolutely anything. From expletives to slang, the words you will find in the data may be a challenge to interpret but the text is the closest way to collect the voice of your customer / employee. The Free Text field is likely to contain long, rambling sentences that can simply be analysed. If you count these fields, you are likely to have one of each entry each. Therefore, simply counting the entries will not provide anything meaningful to your analysis. The value is in
Read more