Preppin' Data

Challenge by: Ross Killington This is our next challenge from one of the members of the 38th cohort of the Data School UK, and it's a follow on from last week! After last week's challenge we now have a useful table populated with 4,000 students applying to the Prep School with their respective grades and total scores. This week however it’s crunch time, we need to decide which students will be accepted into the school for the new academic year.  We are supplied with some new data relating to the students including which course they want to study, as well as details about their address and proximity to the school.  Prep School offers 5 courses:  Business Management Computer Science Psychology Engineering Data Analytics Each course can take 20 students, usually it would be a half and half split between students from each region but this year due to a population imbalance we have been asked to accept a split of 75% East Students and 25% West Students on each course.  To...

Video Solution Solution by Tom Prowse and you can download the workflow here .  The challenge this week comes from Ross Killington and follows the theme of analysing some data from The Prep School. We are looking at admissions for the next academic year and we are trying to identify the top students from each region. Step 1 - West Students First we can input the West Students table and then split the ID field so we have the 5 digits and the region in separate fields.  To do this we can use the split on the '-' separator and this will give us a field for each which we can rename to Student ID & Region. Next we want to transform the Grade field from 1-6 to A-F. There isn't an automatic way of doing this, however we can select each of the numbers in the profile pane and replace them with the corresponding number.  Our West table should now look like this:  Step 2 - East Students Next we want to combine the West and the East student tables together so we can work fro...

Challenge by: Ross Killington This is our next challenge from one of the members of the 38th cohort of the Data School UK. As we approach the end of the academic year, the Prep School, a prestigious science & engineering focused sixth form in the imaginary city of Dataville is taking applications for the 2023/24 academic year starting in September. The city is divided into 2 regions, the West and the East, each containing a range of secondary schools. Rather than the students applying on their own behalf, each region puts forward their top 2000 students for consideration. From this total pool of 4000 students, Prep School will only take on 100, making it a highly exclusive institution.  In part 1 of this challenge we will be creating an organised data table showing all of this year's applicants with their respective grades to allow the school to decide who will be accepted.  Because the two regions of the city work independently, the application data from each region varie...

Solution by Tom Prowse and you can download the workflow here . This is the second part to the challenge from Andreea Scintei from last week. We are taking it a further and doing some more analysis on the students. Step 1 - Group Grades We want to input the Results table and then we can transform the shape by using a pivot step to pivot the subjects from columns to rows: Once we have the subjects in a single column, we can use the Tile function to make the groups based on subject and grade The table should now look like this:  We can then join the Tiles input table to our workflow so that we can then replace the group numbers with the associated label. Join the tables with an inner join where Group = Number:  We can then tidy the table so that it looks like this:  Step 2 - Combine Student Info We can now combine our final table, but first we need to ensure that our current workflow is in the correct shape. Although we pivoted our table previous, we can now pivot it back t...

 Challenge by: Andreea Scintei This is our next challenge from one of the members of the 38th cohort of the Data School UK, and it's a follow on from last week! As part of your task to help the Prep School evaluate performance, you discovered that students in classes 9A and 9B seem to struggle the most. You are now tasked with creating a list of all students and the percentile range of their results for each subject. For students that are in classes 9A or 9B you also need to add a Flag column in the output. Inputs 1. Student Information - from last week 2. Student Results - from last week 3. Tiles - to help us categorise the grades Requirements Input the data For each subject, split the grades into 4 groups Hint: use the tile function Replace the tile number with the relevant value from the Tiles input Join the data with the Student Information Trim Class field Create a flag column for students in class 9A or 9B, who are in the lower quartile for 2 or more subjects Filter to just t...