Posts

Showing posts from September, 2020

2020: Week 40

Image
Challenge by: Jenny Martin I often see dashboards and wonder about the data prep behind them. Sometimes the most beautiful of dashboards can be hiding the most horrendous of data preparation. Let's take this Viz of the Day from dataschooler Matthew Armstrong . The visualisation itself is fairly simple, but how did the data start off?  Explore Matthew's viz here Inputs There are three inputs this week: The poems, scarped from everypoet.com The Scrabble scores for each letter (Optional) Scaffolding list Requirements Input the data Lines of the poem will not contain any HTML, css or js e.g. <head>, e9=new Object() etc. Filter out any rows which are not lines of the poem Wordsworth is very original, so there shouldn't be any duplicate lines in our data set. Filter out any repeated rows The first line of each poem is also the title of the poem. Ensure this is the case and number the lines of each poem Split the data out so there is a line for each word and assign a word ...
Read more

2020: Week 39 - Solution

Image
Solution by Tom Prowse and you can download our solution here .  This week we took a deeper look at Jonathan's latest viz which focussed on Pret a Manger's new deal in the UK. However, we have some slightly more complex orders therefore we will use Tableau Prep to take a deeper look to see if the deal is worth while.  Step 1 - Restructure Orders First we are going to focus on the Orders input, and restructuring the table so that we have a line for order for each person. Therefore the first step is to use a Pivot tool to bring each of the day's into a single column:  We can use a Wildcard pivot on 'day' which will automatically bring all fields which contain that word.  After the pivot our table looks like this:  So now we want to clean this up a bit, therefore we can remove any null values from the Pivot1 Values, and then split each of orders into a separate column. We can split using a custom split by ',':  We now have a separate column for each of our dr...
Read more

2020: Week 39

Image
Challenge by: Jenny Martin Last week, Jonathan created this amazing viz, allowing you to investigate Pret a Manger's new deal in the UK: Explore the viz here The premise, as explained in Jonathan's viz, is that you can order up to 5 drinks a day, every day, for £20 a month. Unfortunately, the Preppin' Data team have slightly more complex orders than the viz allows you to input. So we'll need to use Tableau Prep to see if the deal is worthwhile for everyone and how much they could save! Inputs There are 2 inputs this week: Orders Price List (based on St Albans 15/09/2020) Requirements Input the Data Restructure the orders so we have a line per person per drink, with a count of how many times they order that drink in a week Remember: extra shots or syrups will have their own prices so should be on a separate row in the data Restructure the price list so we have each item with its price on a separate line Join the ordered drinks to their prices Beware ordered drinks not h...
Read more

2020: Week 38 - Solution

Image
Solution by Tom Prowse and you can download our solution here .  The challenge this week was to see if each of the given word pairs were anagrams of each other or not. There were lots of different solutions on Twitter, some long some short and it was great to see everyone learning different techniques from each other! Step 1 - Split Words The first task that we want to achieve is to split the words so that each of the letters are on a single row. This isn't something that is native within Tableau Prep so there are a few steps which we will need to cover first.  To start we want to give each of our word pairs a row ID. The order doesn't matter here therefore we can use the Rank feature to give each row a unique ID:  Row ID After the rank we now have a row ID for each row:  At this stage we are going to split the workflow into two separate branches, one for each of the words. These are identical so we will cover the Word 1 branch and this can be repeated but for Word ...
Read more

2020: Week 38

Image
Challenge by Tom Prowse This week we are going to have a bit of fun and taken inspiration from one of the Alteryx Weekly Challenges. For this week's challenge we want to build a workflow that will allow us to identify whether two words are anagrams of each other.  We have selected some words that are related to Tableau Prep & Preppin Data, so can you tell if they are anagrams or not? Input One file with two tables:  1. Words - the list of words to determine whether they are Anagrams or not. 2. Scaffold - this is an optional input to use if you need it! Requirements Input the Data Determine whether the words are Anagrams. There are the following rules:  Anagrams are formed by re-arranging of another word (on the same row) All anagrams are one word only No letter can be used more than once All letters must be used Output Data Output One Table:   Three Fields Word 1 Word 2 Anagram? 12 Rows (13 including headers) After you finish the challenge make sure to fill in t...
Read more

2020: Week 37 - Solution

Image
  Solution by Tom Prowse and you can download our solution here .  This week we looked at the classic question, that can be a bit tricky in Tableau, about how to calculate working days. Working days traditionally exclude any weekends and bank holidays and are used in various business reports, especially with anything around productivity and scheduling.  For this challenge we wanted you to take a custom start date and work out how many working days it has been since that date. In the original challenge post, Jenny had selected a start date as 1st April 2019, so we'll continue with that date in this solution post as it was the date I started at The Information Lab as well! Step 1 - How Many Days? The first step is to calculate how many days there have been since your selected start date. For this we will create a 'Today' field to use as our end date (this can be any date that you want to use).  Today Today() Now we have a start and end date, we can calculate how many ...
Read more