Posts

Showing posts from April, 2020

2020: Week 18

Image
Due to the social distancing and isolation caused by Covid-19, all football games have been postponed across the majority of the world. Therefore to help fill the void, this week’s challenge is all about analysing football team line-ups and in-particular the Premier League games for Liverpool. We start with a nicely formatted spreadsheet that includes the following: Match Details - Including dates, teams, location and formation Match Day Squad - 18 players broken down into Starting XI & Substitutes Starting XI - the 11 players who started the game for Liverpool Substitutes - the 7 players who started as a substitute for Liverpool Substitution Information - Includes the Player On/Off and the Time From this we want to answer some questions about the playing time of each of the players. Requirements Input Data Update the headers for Match Details, Starting XI, Substitutes. Ideally this will be done without just renaming each individual header separately. Note, for su

2020: Week 17 - Solution

Image
You can find our full solution workflow below and download it from the Preppin' Data Community page ! This week we looked at survey results about what Netflix shows people are watching. We want to find out what shows are being watched, how they are rated and on what devices they are being watched on. Remove Duplicates First, we are going to input the Form Responses table to start our workflow. At this stage, we don't require the rating fields, therefore we are going to remove these straight from the off (we'll bring them back in later). Our input step looks like this:  The next step in this challenge is to remove any duplicate entries, where someone has answered the survey twice but with the same answers each time. The first step is to use an LOD analytical calculation to find the first time someone had responded.  Within this calculation we are saying, for each Respondent return the Min from the Timestamp field.  We can now use an aggrega

2020: Week 17

Image
Time's are tough at the moment and Netflix usage is high. We've all got that fear as we come to the end of a series we've been binging - what should I watch next?! So we decided to make this week's challenge all about helping to answer this question. We took a survey of colleagues to find out what they've been watching, what devices they've been using and how they would rate these shows. Unfortunately, the survey was thrown together rather hastily and there's definitely some clean up to be done!   As an extra challenge, typed calculations are forbidden!  That's right, we've got another CLICK ONLY challenge for you to really showcase the power of Tableau Prep. Remember to keep an eye on the recommendations and you are allowed to rename things. Check out this post of all the other click only challenges for inspiration if you're getting stuck! Inputs There are 3 inputs this week: Netflix Survey Results List of all content on U

2020: Week 16 - Solution

Image
You can find our full solution workflow below and download it from the Preppin' Data Community Forum page! Step 1 - Input Files Our first step is to input both of the tables that contain sales from our Lewisham and Camden stores. We can do this by using Tableau Prep's wildcard union feature. Within the setup we are going to include all sheets with the matching pattern '*Sales'. This looks like this:  This will automatically pick up any additionally stores added in the future, as long as they end with 'Sales'.  Step 2 - Clean Sales & Profit The next step is to clean fields, so that we are able to create one column for Sales and one column for Profit. The first task is to tidy a couple of columns after the wildcard union. We can remove the 'File Paths' field, we are going to remove the word 'store' after Camden/Lewisham. We can do this easily by using the Custom Split functionality, and splitting off the First values be

2020: Week 16

Image
Last week, I ran a series of webinars on how to 'Think like a Data Prepper'. The webinars were recorded and can be viewed on YouTube here . For the fourth webinar, ' No Prepping Required ', includes a data set that would be impossible to prepare just within the Data Connection screen of Tableau Desktop. Therefore, we thought it would be fun for you to solve in Prep instead! *hint* If you are new to Tableau Prep, check out the fourth webinar as I run through the solution and talk you through some of the key steps if you get stuck, Requirements Store Sales and Profits Store days worked Input the data Create Sales and Profit columns (one for each measure)  Create a date field Add on the number of days worked by staff in each store Only add staff numbers where store sales and profits exist Remove excess fields Output the data Output One file: 7 Fields: Store Category Scent Date (One row per month - there should be four months)