Preppin' Data

At Chin & Beard Suds Co (our Preppin' Data fake company), one of our non-executive directors has asked about the effects of Valentine's Day on our store sales. Sadly, the non-exec director isn't very good with Table Calculations in Tableau Desktop so we have promised a data set to allow them to build their own views with all of the calculations pre-computed.

Here's the dashboard the non-exec director wants:
Here's my version
Requirements: Input the DataDetermine if the sales date is pre- or post- Valentine's dayWork out the running total of sales for each store, restarting after Valentine's day to allow us to compare the two 14 day periods Output: One data set (and dashboard for bonus points - remember no table calcs!)Your output date should have:56 rows (57 including headers)5 columns:DateStorePre / Post Valentine's day flagDaily Sales ValueRunning Sales Value (remember this should restart per store and post-Valentine's day)For comparison, here…

You can view our full solution workflow below, download it here, or view the walkthrough video further down!


This week we’re using straight out-the-box Tableau Prep and taking away the power of writing calculations in order to highlight some of the great features that are built into it. Some of the things used within this challenge are:

Splitting a field up into multiple fields.Pivoting columns to rows.Filtering data.Cleaning up data (optional).Aggregating data.Joining data together.
And all this without having to do any typing other than renaming some fields for clarity! This will be quite a simplistic write-up, but that’s just a reflection of how simple all these tasks are once you know how to do them.
Splitting up the ingredients. Click on the [Ingredients] field and click ‘Custom Split…’ (either from the profile pane toolbar or by clicking the ‘More Options’ dots on the field itself & clicking ‘Split Values’). Set the separator as ‘,’ and set the split settings to ‘All fields’.

…

Growing up in the 90s, one word instantly draws to mind an image in my head. The word is Pivot and the image is:
image from: Growth Hackers So why do we need to understand about pivoting data (not sofas in stairwells) for data analysis? When do we need to Pivot in Tableau Prep? Tableau Desktop needs the data to be in structured columns. Each column can hold one type of data and can then be used on either shelves or cards in Desktop to alter the view on screen. 
For example, a common need for pivot is when a new column is added for each new date in a data set. The reason why this data structure isn't great in Tableau is two-fold. One, if I want my dates to form an x-axis, they can't with this data structure as I need one data field (column) to hold all the different dates and another column to hold all the relevant values for each of those dates. Secondly, if the file is updated, Tableau won't automatically add the new column in to your analysis. If the new data appeared as …

This week's challenge is to NOT USE TYPED CALCULATIONS in Prep to solve the challenge. No cheating with copying and pasting formulas either - just use Prep Builder's functionality.

Last night, we went to have a burger at a local restuarant (hi Brickfields in Brockley!!) and I noticed their cocktail menu looked ripe for a data prep challenge. What I wanted to understand is the cost of the ingredient linked to the price of the cocktail?

Requirements  Input the data fileName the columns 'Cocktails', 'Ingredient' and 'Price respectively Seperate the ingredients and create a single column of themWork out the position of the ingredients within the list of ingredients in each cocktailWork out the average price of the cocktails that ingredient is used inAdd the original cocktail price back inOutput the file Output 5 Columns53 Rows (54 including headers)
For comparison, here's our output files. Don't to forget to fill in our participation tracker!

You can view our full solution workflow below and download it here!

This week saw our first collaboration with Workout Wednesday! For the Preppin’ Data solution, the order of steps is very interchangeable this week – as long as you join on the correct fields it should work regardless of whether you join at the very start, very end, or somewhere in the middle. There are two particular steps I’ll cover here:
Obtaining all the [Fellow Artists].Removing duplicate records. However to view a full walkthrough of the solution check out the video below.

Obtaining the [Fellow Artists] This is probably the hardest part of the task. I’ll outline the steps below but you can find it demonstrated in the solution video.

If the [Concert] field contains a ‘/’ then it is a list of artists. If it doesn’t include a ‘/’ then it’s a concert name like “Divide World Tour”. We want to both filter out these concert names as well as Ben and Ed’s names so that our final [Fellow Artists] field only contains blanks …

When Lorna suggested we set-up a challenge for Tableau users to not only complete a Workout Wedesday live but use Tableau Prep Builder to prepare their data then we jumped at the chance to collaborate. This week is held as a live session (I'm waving if you are in the room and if you're not here, you're welcome to take part too) so we have built a combined challenge that should only take a few hours in total even if you new to either tool.
So what's the challenge? To celebrate Tableau's Music month Lorna found a great data source on one of her favourite artists (Ed Sheeran) that led me to ask a question about one of my faves (Ben Howard). We want to analyse the two artists careers based on their touring patterns and as two UK-based singer-songwriters who appeared on the UK music scene at similar times, how have they developed.
The Preppin' Data part We have taken the gig history from concertarchives.org and done a little pre-cleaning as we wanted the challenge t…

You can view our full solution workflow below and download it here!


This week saw our first official foray into the world of RegEx. There’s a lot to learn when it comes to RegEx and some great resources to help are https://regexr.com/ & https://regex101.com/. In this solution post I’ll highlight the functions and RegEx we personally used as well as the overarching method for our solution. I'll break down the RegEx within the first calculation, however for the others and for a better understanding I'd recommend using one of the aforementioned resources which help break them down - I find Regex101 is particularly good for this.

Use REGEXP_EXTRACT() to find and pull out informationREGEXP_EXTRACT() is a function that allows you to look within a string and extract the first instance of a specific combination of characters. It takes two parameters:
The string/string field.The RegEx that defines the character string you’re looking for. Take the [Name] field we wish to create for e…