Preppin' Data

 Challenge by: Beth Kairys A couple of weeks ago, at the Tableau Prep User Group, co-leader Beth Kairys presented a brilliant workflow which covers a lot of the fundamentals of data prep. Luckily, she agreed to allow us to turn it into a Preppin' Data challenge, thanks Beth! If you missed the session or want a sneaky peak at the solution, then click here to watch the user group recording. The purpose of the challenge is to get our cases data into a format where we can reproduce the following chart (which you're welcome to do as a bonus exercise too!) Inputs There are 3 inputs for this challenge: Business Unit A cases  Business Unit B cases  Risk Level lookup table  Requirements Input the data From the Business Unit A Input, create a Date Lodged field Use the lookup table to update the risk rating Bring Business Unit A & B together We want to classify each case in relation to the beginning of the quarter (01/10/21): Opening cases = if the case was lodged before...

  Solution by Tom Prowse and you can download the workflow here.  This week's challenge took a return to the Allchains Bike Store for another challenge using the create new rows feature from the latest Tableau Prep release. The challenged focused on how we can make an estimation of how much money had been raised from a charity fundraiser where the data hadn't been recorded accurately. Step 1 - Create New Rows The first step is to input the data and then we can use the create new rows step to fill in any date that is missing between the first and last date in the data set.  The setup required to create these new rows is as follows:  We can use the values from the Date field, where the start date is the earliest date and the end date is the latest date, and we can update the existing fields when adding the new rows. This should be on a 1 day increment and the new rows should copy the values from the previous row.  Our updated table should look like this:  St...

 Challenge by: Carl Allchin This week we are going to use one of the newer features in Tableau Prep, New Rows. New Rows was released in Prep Builder version 2021.3 so you will need that version or later of Prep for this challenge (unless you want to scaffold your data yourself).  To date, Tableau Prep has assessed each row of data separately but we all know data can often be incomplete and that made for some tough challenges. With the New Rows step within Prep Builder this should make working with incomplete data sets much easier (and in some cases possible where it wasn't before).  If you want to learn more about the New Rows step and how to use it, have a look at this 'How to...' post on our new site: https://www.preppindata.com/howto/how-to-create-new-rows This challenge isn't just for Prep users. Jenny, Tom and I love to see solutions on all different types of software and in different languages so please feel free to use whatever you want to as it helps everyone...

Solution by Tom Prowse and you can download the solution here. This week we are looking at football data with a focus on the historical results for Southend United. Southend had only won 18 times in their last 108 games, so we look back at the historical results which were a lot more promising compared to recent times.  Step 1 - Tidy Input The first task after inputting the data is to clean some of the fields, including renaming the P1 field to Pts and excluding any null rows. We can rename the P1 field right in the input step by double clicking on P1 and then renaming to Pts We can use a filter to remove any of the Null values from the Season field, and our table should now look like this:  Next we can create the special circumstances field using the following calculation:  Special Circumstances IF ISNULL([POS]) THEN "Incomplete" ELSEIF [POS]="ABAND" THEN "Abandoned due to WW2" ELSE "N/A" END Then the final part of initial cleaning is to make s...

Challenge by: Jenny Martin I was talking to a friend the other day who is a big football fan, but unfortunately their team hasn't been doing too well in recent years. They support a local team called Southend United and my ears pricked up when he claimed they've only won 18 games in the last 108 that they've played. My initial intention with this challenge was to investigate this claim, but instead I stumbled upon historical data which I though might help him to feel a little better and remember the good times. Input We're using the Football Club History Database  for our data this week: Requirements Input the data Rename the penultimate column from P 1 (as it appears in Prep) to Pts Exclude null rows Create a Special Circumstances field with the following categories Incomplete (for the most recent season) Abandoned due to WW2 (for the 1939 season) N/A for full seasons Ensure the POS field only has values for full seasons Extract the numeric values from the leagues F...

Solution by Tom Prowse and you can download the workflow here.  This week we some pet data from the Austin Animal Center  and we look at how successful they are at find new adoptions for their animals.  Step 1 - Filter for Cats & Dogs First we need to input the table from the input, at this stage we can remove the duplicated date field from the input options by unselecting MonthYear: Then create a new step where we can use the filter to 'Keep Only' cat and dog from the Animal Type field. We can do this by selecting both values and then pressing the 'Keep Only' button. Step 2 - Group Adopted & Other Next we want to group all of the different outcomes so that we have two categories:  Adopted, Returned to Owner, or Transferred  Other We can do this by using the grouping functionality within Tableau Prep. There are a few ways of doing this but we have used a manual grouping to select the 'Adoption, Returned to Owner, & Transfer' values from the Outcome ...

 Challenge By: Jenny Martin I'm sure if we took a poll, there would be a large number of us who have become pet owners in the past couple of years. I myself have become the owner of a little kitten called Pyrrha.  It got me wanting to play with some pet data and I came across Austin Animal Center. They claim  over 90% of animals entering the center, are adopted, transferred to rescue or returned to their owners, which is impressive and a statistic we can validate for ourselves! Input Feel free to grab your own up to date data from the link above, or use this extract that we have downloaded: Requirements Input the data Remove the duplicated date field Filter to only cats and dogs (the other animals have too small a data sample) Group up the Outcome Type field into 2 groups: Adopted, Returned to Owner or Transferred Other Calculate the % of Total for each Outcome Type Grouping and for each Animal Type Output the data Output 3 fields Animal Type Adopted, Returned to Owner o...

Solution by Tom Prowse and you can download the workflow here.  The challenge this week was all about parsing information from a single column of data, with a focus on a painting process from our Bike store.  Step 1 - Make Date Time Field The first step this week is to combine the Date and the Time field to create a datetime that each of the changes occurred in the process. This can be easily created by using a MAKEDATETIME() function:  Datetime   MAKEDATETIME([Date],[Time]) Then we can remove the individual Date and Time fields.  Step 2 - Bike Type & Batch Status We can now start to identify each of the different areas that the engineers are interested in. This includes the type of bike and whether the batch failed or not, and these are identified by the Data Type = 'Result Data'.  After filtering the data set so we only have the Result Data fields, we can then pivot these so they each have a separate column:  At this stage our data set should lo...