Preppin' Data

This challenge is a collaborative effort from Jenny, Carl and Lorna . After the success of last year's Preppin' Data and Workout Wednesday live event, we decided a second meeting of the two challenges was a must. Although we can't be together physically in person, thank goodness we're able to host the event virtually! Unfortunately, the Olympics that were due to take place in Japan this year weren't so lucky. Therefore, we chose to focus our challenge on looking back at historical Olympics data. Did you know this isn't the first Olympics to be cancelled? Inputs There are 3 inputs this week:  Host Countries (which you may recognise as the output from week 28's challenge , whose input came from Sports Viz Sunday )  The history of all the medallists for each Olympics (sourced from the Guardian  and Wikipedia ) A country codes lookup table. Requirements Input the data Make sure every medal has both a Country and Country Code associa...

You can view our full solution workflow below and download it here ! Out full solution workflow. 1. General preparation: pivoting statuses. The first stage towards all four outputs is to get all the [Date]s for a single [Order] onto the same row, giving each [Date] the appropriate [Status] field name. To achieve this, simply use a Row-to-Columns pivot. Put [Status] onto ‘Pivoted Fields’, and put the MIN or MAX of [Date] onto the aggregation section We can take MIN or MAX as there is only one [Date] per [Status] per [Order], so MIN & MAX are one and the same. Pivoting the statuses. 2. Get preparation: getting the time between dates. The second stage of general preparation is to calculate the [Time to Send] and the [Time to Review from Sending Order]. To calculate these we can use the DATEDIFF() function to get the days between [Purchased], [Sent], and [Reviewed] [Time to Send] [Time to Review from Sending Order] DATEDIFF('da...