Preppin' Data

 Challenge By: Jenny Martin Data preppin' ideas really are all around! I was hanging some pictures the other week and I had some weird and wonderful sizes, so working out the perfect frames was a bit tiresome. If only I could use a data preppin' tool to speed up the process...  Inputs We have 2 inputs this week: Picture sizes  Frame sizes    Requirements Input the data Split up the sizes of the pictures and the frames into lengths and widths Remember an inch is 2.54cm Frames can always be rotated, so make sure you know which is the min/max side See which pictures fit into which frames Work out the area of the frame vs the area of the picture and choose the frame with the smallest excess Output the data Output 4 fields Picture Frame Max Side Min Side 14 rows (15 including headers) After you finish the challenge make sure to fill in the  participation tracker , then share your solution on Twitter using  #PreppinData  and tagging  @Datajedininja...

  Solution by Tom Prowse and you can download the workflow here .  This week was the final instalment of the Excel challenges month, so it seemed like the right time to involve a Vlookup and Index matches as these are such popular features. For the challenge this week we want to compare monthly targets with data stored on another sheet... let's see how we solved it! Step 1 - Average Monthly Sales The first step this week is to input the Employee Sales table and then calculate the average monthly sales for each employee.  Before using an aggregate tool to calculate the average, we need to pivot our data so that we have all of the months in a single column, therefore we can use a wildcard columns to rows pivot to bring all of the months through:  Now we have a single column for sales and months, therefore we can use the aggregation tool to calculate the average monthly sales per employee: Our table should now look like this:  Step 2 - Combine Targets & Sales W...

 Challenge By: Jenny Martin As we come to the end of our excel challenges month, it seems only right to think about vlookups and index matches. In Excel, these are great functions for bringing together data from different worksheets without having to manually copy and paste.  Our scenario this week is looking at Employee Sales at Allchains for each month of the year so far and we want to compare this to their Monthly Targets, stored on another sheet. Inputs We have 2 inputs this week: Employee Sales Employee Targets Requirements Input data Calculate the Average Monthly Sales for each employee In the Targets sheet the Store Name needs cleaning up Filter the data so that only employees who are below 90% of their target on average remain For these employees, we also want to know the % of months that they met/exceeded their target Output the data Output 5 fields Store Employee Avg monthly Sales % of months target met Monthly Target 4 rows (5 including headers) After you finish the...

  Solution by Tom Prowse and you can download the workflow here . This week we continued with our Excel themed challenges by looking at how we could add some additional rows within Tableau Prep. In Excel this is easily done, but it takes some slightly different logic to achieve this in Prep so let's see how it can be done! Step 1 - Create One Data Set The first step is to create a single data source which includes all of the different files within the Input folder. Luckily these all have the same structure, therefore we can use the Wildcard Union to stack all of these on top of each other.  We want to bring through all of the files, so we use the * matching pattern to include all files After this we can remove the 'File Path' field, change the 'Table Names' to a date, then rename it to 'Reporting Date'. Finally, we are going to need the Reporting Date field twice in future steps so we can duplicate this field as well.  Our table should now look like this:  S...

 Challenge by: Carl Allchin If you've spent as long as I have in the data world, you will inevitably have had moments when your sophisticated tools are actually a lot harder to solve a challenge with than Excel. The people you work with are likely to describe challenges to you in Excel terms and expect your solutions to be able to follow the same process as their logic. It's not always that easy though.  Last week when working with some client data (I've converted this to an Allchains example), my team was challenged to look at Orders captured in a weekly snapshot that was then exported into Excel.  Each week the file would show any order that was still opened that hadn't been fulfilled (ie delivered to the customer). The challenge is to classify when an order is new (the first report it has appeared in), unfulfilled (when it appears in any subsequent reports) or completed (the week after the order last appears in a report). But what if we needed to know whether the ord...

  Solution by Tom Prowse and you can download the workflow here .  This week we continued with the Excel theme and focussed on the SUMIF function within Tableau Prep.  Step 1 - Flight Details After inputting the data the first task is to form the correct flight name. This is a combination of the Departure and Destination fields so we need to bring these together within a string calculation:  Flight [Departure]+ ' to '+[Destination] We can then remove the departure and destination fields and then calculate the number of days between the flight and the sales date.  Days until flight DATEDIFF('day',[Date],[Date of Flight]) Our table looks like this: Step 2 - Calculate Sales Based on Conditions Within this step we first need to classify if the sales were less or more than 7 days until the scheduled flight date. To do this we want to use the IF function to write some IF statements. First is the Less than 7 days:  Sales less than 7 days until the flight IF [Days ...

 Challenge by: Carl Allchin My partner is an amazing Excel user as are many of her colleagues. When in a pub, a frequent getting to know you question was "What's your favourite Excel function?". As a SQL / Tableau user, after my first meeting I knew I had to up my game. SUMIFS became my go to answer and that is one of the functions we will look to replicate in Prep this week.  SUMIF, or SUMIFS if you have multiple conditions, allows you to scan a data set and summarise the values that match any condition you create. When working with large tables with multiple entries per category, this is a great way to create some totals to help you analyse the data set. Whilst SUMIF doesn't exist within Prep, the IF function and aggregation step can be used to create the same effect.  Excel allows for lots of different types of aggregations so whilst SUMIF was my go-to answer: average, minimum, count etc are all possible too.  The challenge this week is forming the logic in Prep ...