Preppin' Data

Solution by Tom Prowse and you can download the workflow here . We continue to look at some of the new features within Tableau Prep this week, with a focus on % difference from and the lookup function.  Step 1 - Input Data First up we want to input all of the data from the Excel sheet that contains all of the different months of data. To do this we can utilise the wildcard union within the input step to bring in all of the sheets to a single step.  We can then tidy the table by renaming the Table Names field to Month and then merge any fields that have a similar name. You can merge fields by selecting the fields then pressing Merge fields in the grey bar in the profile pane. At this stage our table should look like this:  Step 2 - Dates Next we want to format the date so it is in a traditional date field format. This involves converting the Order Date string and then combining with the month field.  First, we split Order Date using the ',' as a separator and we want ...

Challenge by: Kelly Gilbert Validated by:  Abiramm Thavajothy After a successful HR Month at the end of 2022, Kelly has returned with more real-life (fake data) challenges for us! Since Kelly's data prep tool of choice is Alteryx or Python, we also enlisted some help from colleagues at the Data School to ensure the logic works in Tableau Prep as well. Thanks for your help with this one Abi! Over to Kelly: Over the next few challenges, we’ll be using fake employee data for a small network of distribution centers. The data is fake, but the challenges are based on real people analytics challenges. In this challenge, we have two HR tables: one that contains the list of employees (with their name, DOB, etc.), and one that contains a monthly snapshot of which employees worked at each DC during the month. We want to be able to join the two tables, but sometimes the employee_id is missing from one (or both!) of the tables. We do have a second ID (the guid), but sometimes that is missing as...

Challenge by: Jenny Martin For this challenge we're continuing to play around with the new features in the latest 2023.2 version of Tableau Prep. These will be: % Difference From  Lookup function Both these calculations will help us work across multiple rows of data in slightly different ways. We'll be using them to help AllChains analyse the performance of their Stores over the last financial year. Input We have one Excel file that contains a sheet for each Month of Sales.  Requirements Input the data Bring all the Months together into a single table Merge together misaligned fields Create an Order Date field The financial year for Allchains starts in July. Convert the dates to Fiscal Quarters Aggregate the data to a Quarterly level for each Store, ignoring the product details Calculate the % Difference in Sales from the Previous Quarter Multiply this value by 100 and round to 1 decimal place Use the lookup function so that we can compare the % Difference for all Quarters in ...

Solution by Tom Prowse and you can download the workflow here . This week we take our first look at some new features within Tableau Prep! These include the Lookup Function, Moving Averages, and adding a TimeStamp to a file name when we output the data. Step 1 - Filter Dates First we want to input our data table, then we need to ensure that the date field is in the correct format. We only want to look at dates on a monthly basis, therefore we can use the DateTrunc function to ensure that all dates are rolled back to the 1st of the month. Date   DATE( DATETRUNC('month',[Date])) We can then use a filter on this field to remove any dates after July  Now our table should look like this:  Step 2 - Monthly Sales We can now aggregate the table so that we have our sales at a monthly level. Within the aggregation we group by Date, Store, and Bike Type, then Sum Sales & Profit. Now we have the data at a monthly level, we can now fill in any months that might be missing.  F...

Challenge by: Jenny Martin Since the 2023.2 version of Prep has been out for a while now, we thought it was high time we played around with a few features! These will be:  Lookup function Moving Average Adding a time stamp to the file name We'll be using them to help AllChains perform a monthly analysis across their Stores.  One of the key things to watch out for when using a Moving Average is that you have a complete dataset i.e. when you're averaging across 3 months, those 3 months should be consecutive. If not, you're going to want to make sure to scaffold your data, so you have a row for each month, even if there were no sales in that month. Input One input this week containing AllChains sales data: Requirements Input the data We only want to consider complete months so filter values up to June 2023 The values after July 2023 are forecasted values Aggregated the data to a monthly level for each Store and Bike Type Notice how not every Bike Type has a sale in every Store...

Solution by Tom Prowse and you can download the workflow here . We have another challenge focussed on the Prep School this week but this time we are looking at the athletic abilities of our students. The challenge is from  Habeeb Gayle  so lets look how we can solve it. Step 1 - Combine Tables First we want to combine all of our tables together so we have a single table to work from. First we want to join the Students and the Track Times using an inner join on the ID field: Then we can join the Benchmarks table using an inner join where Gender = Gender, Age = Age, Track_Event = Event:  When we've joined these tables we can remove the duplicated fields and the table should look like this: Step 2 - Filter Results We can now start to filter our results to meet the requirements. First we want to remove anyone who hasn't met the benchmark by using a filter calculation  [time]<=[Benchmark]. Next, we want to filter to remove any errors. We can highlight any rows which ar...

Created by: Habeeb Gayle This is our next challenge from one of the members of the 38th cohort of the Data School UK.  The Prep School is keen on developing athletes from amongst their students. They want to identify and rank the top 100m and 200m athletes. Each student listed has taken part in either the 100m or 200m sprint. Some work needs to be done to find, rank and clean the dataset. Inputs 1. Each student has some basic information about themselves such as age and gender and they have an id number to be identified by.  2. Each student has a track event and corresponding time.  3. These events have a set benchmark that identifies the top students in each age group.  Requirements Input the data Join the students basic information with their event and track time  Join the benchmark table with the right students Note: the number of rows should still be 300 after the join Filter out the students that did not fall within the benchmark There has been an erro...