Preppin' Data

 Created by: Carl Allchin If you have been following our challenges throughout July, you will have mastered the fundamentals of data preparation. Now let's dial those skills up a bit further and start to use a few more advanced functions. We'll also offer you a little guidance than before. This week will focus on your user choosing the output they need. For this challenge, our sales team in each of our store's need to be able to download the top 10 liquid products, size and scent combinations. To save yourself time and effort, try to do this with one flow and use a parameter that your user can select their own store.  Input Let's use the same input again from week 27. If you haven't got it to hand, download it from here . Requirements Input the data Split the Product Name field into Product Type and Size Only keep the Liquid products Total up the sales for each Product Size and Scent for each Store Rank each of the Product Size and Scent combinations for each Store ...

Solution by Tom Prowse and you can download the workflow here .  This is our final week of fundamental challenges throughout the month of July and the last challenge helps to combine a few of the techniques that we've used along the way.  Step 1 - Union East & West  The first step this week is to focus on the fundamental technique of combining data sources via a Union. Unions allow us to 'stack' tables on top of each other, so is perfect for when we want to extend our data set especially when they have a similar field structure. As we are looking to combine the East & West Sales Person tables, the union is the perfect choice here as they have the same fields in both tables. After bringing both tables into our workflow, we can drag one on top of another and create the union step. As a result of the union, Tableau Prep creates a Table Names field that can sometimes be used to parse out key information that can be used at a later stage. In this example we want to ext...

 Created by: Carl Allchin Welcome to the final week of beginners' month in July.  Chin & Beard Suds Co (C&BSCo) have been recording the percentage contribution of the top 3 sales people in each of their stores. They want to know how much value this actually equates to. This week's challenge will let you experience one final technique we haven't used this month yet, Unions.  Here's more support with Unions if you are new to them: How to Union in Prep Union theory Input There are four files you will need to input this week.  There are two inputs for Sales Data in each of our Regions (East and West). They are the same structure of data fields: The third file is a Store ID lookup table: The fourth file is same as the original input for Week 27: Requirements Input the 'Top 3 Sales People per Store' for both regions: East & West Combine these files Bonus challenge for experienced Preppers - take the Region Name from the File Name. For newer Preppers, use...

Solution by Tom Prowse and you can download the workflow here .  The next instalment of our introductory month contained another key technique that is fundamental to data prep - Joins. The challenge we want to combine our targets data with our original data source so that we can expand our analysis. Let's see how to solve it! Step 1 - Sales Input The first step is to input both of the data sets into the workflow. Once we have connected to the Sales table, we want to extract the product type from the Product Name field. To do this we can use a custom split on the '-' to split the first value from it: We can then rename this field to Product Type.  After extracting the product type, we want to aggregate our table so we can see the total sales for each Product Type, Store Name, and Region combination: At this stage we have prepared all we need to you and our table should look like this: Step 2 - Targets Input We now want to turn our attention to the targets input. Currently, w...

 Challenge by: Carl Allchin  This week's challenge is a continuation of another introductory month for newer Preppers.  When you work with data, you are likely to increasingly ask more and more questions as you find out new insights. These additional questions often means you need to combine data sets together. This uses a technique called Joins. For this week's challenge, you might want to use the supporting links if you haven't joined data sets together before.  If you are newer to preparing data then you might want to use the following links to learn these techniques: Pivot Joins String calculations Input Two files : 1. The same input as Week 27 & 28 2. Targets (k's) Requirements Input both data sets Remove unnecessary values from the Product Name field to just leave the Product Type Total Sales for each Store and Product Type Change the Targets data set into three columns Product Store Sales Target (k's) Multiple the Sales Target (k's) by 1000 to create ...

Solution by Tom Prowse and you can download the workflow here .  This week we continued our fundamental challenges by focussing on a common problem in data preparation. That problem involves including data about something that doesn't happen. Our example is we're trying to identify the days when there were no sales at our stores. Let's see how we solved the challenge. Step 1 - Days with no sales First up we want to convert our date field from a datetime to just a date. This can be done by pressing on the calendar and clock icon in the top left of the field in the profile pane, and then changing to a Date option. We can now go and add some new rows for days when there were no sales. For this use the 'New Rows' step to help us. After bringing the New Rows into our workflow, we need to set this up so that we are creating a new row from our minimum to maximum sale date:  This will allow us to fill in the missing days and will create a new row for each of them. Our table...

Created by: Carl Allchin One of the hardest challenges with data preparation is recording something that doesn't happen. There are some neat tricks and techniques to do exactly this so here's your chance to practice.  Chin & Beard Suds Co. (C&BSCo) is trying to find how many days none of their stores actually sold any products. Thankfully for C&BSCo there aren't too many but the management team want to know which day of the week the non-sales days most commonly happen on.  If you are newer to data preparation, you might find the following links helpful: New Rows Working with Date data types Input The same input file as last week: Requirements Input the file ( download here ) Convert any data types required (nb. certain Steps in Prep Builder can only utilise limited data types) Create a new row for each day that doesn't have a sale Remove any date record where a sale occurred  Create a column for Day of the Week For each day of the week, count the numbers of...