Preppin' Data

Following the success of Chin & Beard Suds Co, we’ve been informed by our distribution company that they’re having some technical difficulties, so we’d like to step in and offer our professional assistance. The Scenario A shipping distribution company transports valuable cargo for customers all around the world using three different types of boat. They keep the basic details for each departure in a table called Departure Details. A snippet of the Departure Details table. They sell and allocate space on their departures based on the weight and volume of the cargo. They keep the details of each allocation in a table called Allocation Details. A snippet of the Allocation Details table. In the last 3 months they’ve begun noticing issues where more weight or volume is being allocated than the boats can actually carry. This means customers are turning up at the port with cargo which the company is then having to turn away and refund. They want to identify and flag departures...

Welcome back to the solution for week 6 of Preppin’ Data. Firstly, Carl and I would like to thank everyone for the free labour you have all given in helping run the Chin & Beard Suds Co business. It was much cheaper than finding and hiring an external data analyst. In terms of actually completing this challenge there were 3 main elements: Joining the " Soap Pricing Details " with the " England - Mar 2019 " data. Calculating the profit and aggregating it to the country and soap type level. Unioning the newly aggregated data to the " Company Data " table. At any point before, during, or after the previous elements, adding the date information for the new data. You can view our solution below and download it here . The full solution workflow. So let’s dive in. Joining the soap product information with the city sales data. No hidden tricks here, simply joining the product details table to the city sales data on [Category] = [Type of ...

For week 6 of 2019, Carl and I are going to get you playing with aggregation and, more importantly, introduce you all to our side hustle: Chin & Beard Suds Co – esteemed purveyor of (fake) luxury soap products. The Scenario: Chin & Beard Suds Co (C&BS Co.) is looking to update their company data table which details the monthly profit generated by each product in Scotland and England. This has already been done for the first three months of 2019 for Scotland, but the March sales information for England has yet to be aggregated and added to the master table. The March sales information for England is in its own table and contains how many units of each product type were sold in each city. The pricing and production costs for each product type are also contained in their own sheet. The Requirements: Import the file. Make sure to use all 3 sheets. Use the soap pricing details to calculate the profit in each city in England in March. Add the missing overa...

Welcome back for the solution of week 5’s challenge! I’ll preface this by saying this week’s challenge was probably the biggest head-scratcher for me so far, mostly due to trying to thinking of the best way of 1) handing the ‘th’ in “19th July 19” and then 2) adding the weekday to the date to get the [True Date] . This week is also one of our biggest examples so far for proving the statement “there’s no one single correct answer” as there’s a ton of different ways to handle various parts of the challenge. For a couple of stages of this challenge I’ve decided to present two alternate methods; one using RegEx and one without using RegEx, to help accommodate all tastes and perhaps offer a reason to start dipping your toes into the RegEx world. You can see my solution flow below and download it to give it a prod from here . Where it splits and re-joins is simply show-casing the alternative ways mentioned above to handle the same stage – branches and joins are not required for a solutio...

Hands-up all of you who have a system in your organisation that let's your team enter free text answers in to a system? Ok, well that's most of you and I feel for each and every data guru that sits at the end of the database where that information is stored. If you didn't put your hand up, you will have a lot to learn this week! I have often wondered whether I would have a career if it wasn't for projects delivering new operational systems not considering that the 'Junk In' to 'Junk Out' rule is a very pertinent one. Project budget cuts, lack of data awareness and time constraints all lead to a perfect storm of project delivery challenges. One of the side-effects of this is felt as soon as the project releases; how is the new system performing and is it doing what we expected? Welcome to this week's challenge! The input for this week's data is from a small financial services company's contact centre who have to measure some key statisti...

Welcome back for the solution of week 4’s challenge! I wouldn’t put money on this being the last basketball themed challenge so don’t fret if you’re worried that this’ll be a one-time topic. Whilst this week did require some data preparation, the challenge had the unusual goal of using the Profile Pane to recover answers instead of generating a whole workflow to get there. The aim of this was to demonstrate how versatile the Profile Pane can be for quickly generating answers to questions that could otherwise require multiple cleans, pivots, aggregations, etc. To this end, we’ll highlight a few of the cool functionalities of the data pane that could have come in handy for you for our challenge! However, firstly for the impatient among you just looking to check your answers, here they are, but I’d recommend continuing reading to learn more about the functionality used: Our workflow demonstrating a possible solution. Q1. In games won by the Spurs, which player most often scores...

Welcome to week four and in my opinion, I'm three weeks too late for using basketball data. Both Jonathan and I are basketball fans but we have some questions that need solving. Therefore, this week is about using Prep to answer questions using the profile pane (of course you can build a viz if you want to). This is the Profile Pane: The input data comes from ESPN and the way they capture the three major stats on each team's schedule. Hi Points = Most points scored by a single player during the game Hi Rebounds = Most times a player has recovered possession after a missed shot Hi Assists = Most times a player has passed the ball to another player who then scored. This data copies in to Excel really badly... We have given you data just about Carl's favourite team, the San Antonio Spurs. You will obviously need to clean-up the data, but can then use some other functions in Prep Builder to help you answer the following questions. 1. In games won by the...

Congratulations to all of you that have survived week 3 and welcome back! This week we aimed to give you all a taste of scaffolding data in Tableau Prep. We decided to not be truly evil and not only gave you the scaffold but even kindly named the second sheet as “Scaffold” to help point you in the right direction. What can I say, we’re just lovely people. To get more detail about scaffolding and scaffolding use-cases there are a number of blog posts available [1]   [2] [3]  however in short: scaffolding data is a way to create rows of data when the data is missing or empty. In our case, this refers to the fact that we need the payment date for each contract for each month but we only have the contract length and contract start date. If you were to build a scaffold for our data your first instinct may be to create a date scaffold to join our phone contract data to - however by now you'll have noticed we instead have a length scaffold of numerical values. This was a deliber...