Preppin' Data

Challenge by Tom Prowse If you are a fan of football or have been following any of the latest news then you would have probably heard about the proposed new 'Super League' that was planned with a selection of European clubs. This would have involved a group of 12 teams playing in a competition each year without having to qualify or be relegated. The lack of  fair competition between other clubs has caused an uproar among fans, players, media outlets, football associations, and even the UK government!  The 'big 6' English teams to propose the Super League were Arsenal, Chelsea, Liverpool, Manchester United, Manchester City, and Tottenham Hotspur. One of the ideas to try and discourage the clubs from proceeding with the new league, was to threaten the English teams with being expelled from the English Premier League. The challenge this week is to try and understand how the current league table would change if these clubs were to be 'kicked out'. Inputs  The input ...

You can find our full solution workflow below and download it here ! The full solution workflow. Learning Objectives: Practice wildcard unions to manage & speed up inputs. Practice using filter calculations for more precise and versatile filtering. How to workaround the lack of table-calcs in Prep to: Add ranks to your data. Create a top N% filter. Unioning all the relevant data In last week’s solution post we mentioned how using the Wildcard Union could be used to not only quickly import and combine your data but also ensure you only import the data you actually want to import. The Wildcard Union. Add one of sales files to the canvas. Switch to the Multiple Files tab. Select Wildcard Union . Define a “ Matching Pattern ” of “Sales_*”.   This means only files that start with “Sales_” will be including in the Wildcard Union. Click Apply . Obtaining data from just the last 6 months Here we’re also reinforcing lessons from last week by in...

A few weeks back we mentioned that we were cleaning up our mailing lists. Well, now our marketing department is looking to generate further revenue and believes a great way of doing that is rewarding our highest spending customers by emailing them “15 per scent discount” codes! They’ve decided the optimal cut-off for who receives these codes is the top 8% of customers by total sales from orders placed within the last 6 months. Why top 8%? For valid reasons, not just to stop people from mentally working out where the 10% cut off is I'm sure. To help you, they’ve zipped up a bunch of data and sent it over to you (though it seems like in their haste they may have sent some unnecessary files over too). The data contains sales for the last 12 months (as of 24/05/2019). From this they want a list of all the email addresses for the top 8% of customers (by total sales over the last 6 months) along with their rank (by total sales over… you get the idea) and the total sales value. The...