Preppin' Data

Challenge By: Carl Allchin, Jenny Martin & Lorna Brown This challenge comes to you live from the first ever DataFam Europe! We've teamed up with the Workout Wednesday team to bring you a special challenge.  Since we're in London and very close to one of the most popular attractions (St Paul's Cathedral), we thought we could centre the challenge around popular London Attractions and their nearest Tube Stations.  You'll need to clean up the inputs to create outputs ready for Tableau Desktop.  Inputs The inputs for this challenge can be downloaded from either Google Drive (as normal) or Data.World . We have 3 inputs, which can be found in one single Excel File: London Tube Stations  Attraction Footfall  Location Lat Longs  Requirements Input the data For the London Tube Stations table: There are a lot of unnecessary fields, only keep information about the station name and location Clean up the field names There are a lot of duplicate rows. Make sure each row is uniqu

Video Solution Solution by Tom Prowse and you can download the workflow here .  Step 1 - Combine Tables First we want to combine all of our input tables together into a single table. We can do this using two inner joins, first joining the Available Stock and the Product tables using the Product ID:  Then join this table to the Supplier table using Supplier = Supplier ID:  After the joins we can remove any additional fields and our table should look like this: Step 2 - Orders & Quantity Now we have the inputs in a single table we can calculate the Stock and Quantity levels. First we can calculate whether stock has been ordered or not:  Stock Ordered [Quantity Available]<=10 Then we can calculate the quantity ordered:  Quantity Ordered   IF [Stock Ordered] THEN 30-[Quantity Available] END From here we can now calculate the quantity per week using the following calculations:  Previous Week Quantity Available   { PARTITION [Store],[Product] : { ORDERBY [Inventory Date] asc : LOOKUP(

Challenge by: Holly Jones This is the final challenge that DS43 prepared for us earlier this year - over to Holly:

Solution by Tom Prowse and you can download the workflow here . Step 1 - Sold & Returned First we want to tidy the dates a item was sold or returned so that the return date isn't before a sale date. We can identify any issues by creating an issue flag:  Issue? [Date Sold]>=[Date Returned] Then using this to recalculate the Order or Return date:  Order Date IF [Issue?] THEN [Date Returned] ELSE [Date Sold] END Return Date   IF [Issue?] THEN [Date Sold] ELSE [Date Returned] END We can then remove the Issue?, Date Sold, & Date Returned fields. Finally, we can calculate how many days it took to return an item by using a DateDiff function:  Days to Return   DATEDIFF('day',[Order Date],[Return Date]) And also calculate what the value of the returned items are:  Return Value   IF [Days to Return]<=60 THEN [Price ] ELSEIF [Days to Return]<=100 THEN round([Price ]*0.5,2) ELSEIF [Days to Return]>100 THEN 0 END At this stage our table looks like this:  Step 2 - Cal