Preppin' Data

Challenge by: Jenny Martin Data Source Bank (DSB) are looking to introduce a Regular Savings Account for their customers. There are 2 factors they need to decide: The interest rate The maximum amount a customer is able to pay into the account each month DSB are looking to compare other 12 month products currently in the market, to help decide these factors. They want to understand which account offers the customer the biggest opportunity to increase their savings. They also want to educate their customers by showing how their savings would increase each month.  Input 1 file containing the details of 12 month regular savings accounts currently available in the UK Market as taken from Money Saving Expert Requirements  Note: We assume the interest will be spread evenly across the 12 months for this challenge Input the data Update the Max Monthly Amount so it is a numeric field Update the Provider field so it is easy for customers to see in 1 field which accounts have additional conditions

Solution by Tom Prowse and you can download the workflow here . Step 1 - Selected Reporting Year First step this week is to input the data and then create a parameter that will allow the user to select a reporting year.  The parameter should be a whole number with the options of 2020, 2021, 2022, 2023, & 2024 to choose from.  We can then use this in our workflow as a calculated field containing just this parameter:  From here we can utilise this field to create a start and end date for the selected period.  For the start date we use the following calculated fields:  Start DATE(DATETRUNC('week',MAKEDATE([Year]-1,2,1),'Monday')) This will find the Monday before the 1st Feb. End MAKEDATE([Year],12,31) The end date is always the end of the selected year. Step 2 - Reporting Year Next we want to fill out the dates for the reporting year by first calculating the start of the reporting year: Start of Reporting Year DATE(DATETRUNC('week',MAKEDATE([Year],2,1),'Mon

Challenge by: Jenny Martin It's always been interesting to me how reporting dates can be so different for different industries. There is often a table that is centrally maintained explaining how the reporting date translates to the calendar date. This way any dataset can be easily enriched with the reporting dates.  This challenge will be all about creating a dynamically updating reporting date table. Imagine a company uses the following logic: Their reporting year begins on the Monday before 1st February e.g. In 2023, 1st February fell on a Wednesday. Therefore the first day of the reporting year will be 30th January Each Reporting Month contains exactly 4 Reporting Weeks This is the logic we will use for this challenge, but you can imagine how it could be easily updated to fit another organisation's logic. Input The input this week is very simple. We'll update this value so the user can choose a calendar year to generate the reporting dates for. Requirements These will be

Solution by Tom Prowse and you can download the workflow here . Step 1 - Term Data First we need to bring all of the data from the Term tables into a single sheet. We can do this by using the wildcard union within the input step that will allow us to bring the multiple sheets in and combine them together. Within the union we need to ensure that the worksheet filters contain the matching word 'Term*' so that it brings all tables with the word term in from the data source:  Step 2 - Parse Dates Next up we can start to parse and format the date field so that we can create it as a date data type. First we can duplicate the Date and rename to Term Date, then to make sure we have a consistent pattern we can replace any '/' with '.':  Term Date REPLACE([Term Date],'/','.') We can then change this to a Date data type which will convert some of the dates, but some dates don't have a day associated with them so we need to handle these in a different wa

 Challenge By: Ed Hayter This is the conclusion of our Prep School challenges from DS38. Thanks for all your hard work team!  This Preppin’ Data Challenge requires you to process student grades. The Prep School records this information in one excel sheet, with a sheet for each term the report comes from. The reports are in a fairly consistent format with 4 fields for the grades in each subject, but different date formats have been used. The school would like you to prepare two outputs: 1. A table with a student’s average grade (GPA)  across subjects for each term and then a calculated 3 term moving average of GPA to help identify struggling students. 2. A table with just the students who were awarded a prize, the date the award was issued, the 3 term rolling average of GPA that merited the award. Inputs 1 Excel File that contains 6 sheets for Terms 1-6 and a sheet for the Headmaster's Notes  1 csv file that provides a lookup for Student Names Requirements Input the data Bring toget

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Minutes Late After inputting the data we want to remove any null values from the Arrival Time. We can do this by selecting the 'Null' value in the profile pane and then exclude. We can then create the Scheduled Start Time and Arrival Time by combining the date with each of the time fields. Scheduled Start Time MAKEDATETIME([Date],[Scheduled Start Time]) Arrival Time MAKEDATETIME([Date],[Arrival Time]) We can then remove the Date field and then calculate how late each student was by using a datediff calculation:  Lateness DATEDIFF('minute',[Scheduled Start Time],[Arrival Time]) At this stage our table should look like this:  Step 2 - Output 1 At this stage we want to split the workflow into separate branches - one for each output. In our first output we want to calculate the average lateness of students per weekday. We can calculate this using an aggregate step where we group by Day of Week and Avg

 Challenge by:  Adrien Sourdille This challenge is the penultimate of the ongoing series related to “Prep” School provided by DS38. Over to Adrien to explain the challenge: As the Prep School principal, you have heard worrying reports about some of your students being constantly late to class. Naturally, you want to investigate these rumors and get to the bottom of what is happening in your establishment. Luckily, your pupils need to swipe their student cards to enter the building every morning. The attendance log you have access to describes the daily arrival time of 8 students for the year 2022. Null values are generated if the student is absent that day.  You are particularly interested in understanding on what day of the week your students are the latest and which students are “very late” (more than 5 minutes) the most often.  Input 1 input this week with card entry data Requirements Input data Exclude null values from the Arrival Time field. These are days the student wasn't p