Preppin' Data

Challenge by Amalia García-Vellido Santías We have another guest challenge creator with this week's challenge coming from Amalia.  This week we want to analyse the orders that customers have made over a period of time in our restaurant Serendipia. In order to identify how much money we earn each day of the week and also to discover who our top customer is. We are going to be using calculations, pivots and aggregations so lots of the fundamental techniques that are used within data prep! Inputs Menu - contains the menu of the restaurant (notice that the structure is not ideal) 9 fields 10 rows (11 + header) Orders  - each row represents the order a single customer have made at a certain date 3 fields 40 rows (41 + headers) Requirements Input the data Modify the structure of the Menu table so we can have one column for the Type (pizza, pasta, house plate), the name of the plate, ID, and Price ( hint ) Modify the structure of the Orders table to have each item ID in a different r...

This is the last in the 'Starter Challenges' series to get you up and Preppin' to start the new year. We've enjoyed running this mini-series so much we're already looking at creating another similar series later in the year.  This week's challenge involves picking up some more of the fundamental skills and gives you some chances to practice some of the skills you've picked up over the last few weeks. As always, we'll be guiding you along the way with some useful help links if you need a couple of reminders or chance to explore those new techniques.  The new technique for you to learn this week is Joins. If you've worked with different data solutions for a number of years, you'll be familiar with Joins but if you are new you're in for a treat! Joins allow us to bring two data sources together. This allows for much easier, richer and deeper analysis as data is often in many different locations. Use the help links if this is a new technique for ...

In the Chin & Beard Suds Co IT department we are always looking to support our colleagues in the best way possible. As always, we are looking to improve our service and want to understand how the support department is currently performing. We have collected support ticket information from part of this year and want to answer some questions. A new line of data is created every time the status changes. This creates a sequential Status No, giving us a history of the progress each ticket has made. Inputs One file, with the following sheets: 1. Ticket Information Ticket Info for Jan, Feb, March 2. Department SLA Agreements (in days) SLA Agreements Requirements Input Data  - This was updated on 26th Jan (1PM GMT), to remove some duplicate Ticket IDs Clean Ticket ID/Names What is the current ticket status? Are they still open, how long have they been open, and how many business days has that been?  Business days are Monday-Friday, don’t worry about bank ho...

This week we offer two solutions: one for if you have version 2020.1.3 and one for earlier versions - we used 2019.3.2. You can view the full workflows below or use the following links to download them: 2019.3.2 Solution 2020.1.3 Solution 2019.3.2 Solution 2020.1.3 Solution Both flows begin with the same preparation steps so we will indicate when our solution post splits for each specific version. 1. General data preparation This section is for both versions and includes: Removing the averages. Calculating the sample types. Cleaning up the dates. Pivoting the candidates. Removing the null results. It is after pivoting the candidates that the solutions for each version diverge. 1.1 Removing the averages Within a Clean step, locate the "RCP Average" value within the [Poll] field. Right-click on it and select "Exclude". 1.2 Calculating the sample types In the [Sample] field, each value ends with either "LV" or...