Preppin' Data

Created by: Carl Allchin Like any parent of a young person at this time of year in the West, any moment of silence is soon filled with Christmas songs being learned at school. Trying to remember each of the twelve elements in the '12 Days of Christmas' and in order is a unique parenting challenge.  For those who don't know the song, you can find the lyrics here.  This Preppin' Data challenge is to provide you with an ability to have the final verse at hand at all times.  Input There are two inputs : 1. The Gift 2. Additional Description Requirements Input the data Form each line of the lyric and amend any tweaks you might need using calculations Order the lyrics to form the correct final verse Remove any unnecessary fields Output the data Output The output has: 12 rows of data (13 incl. headers) 2 data fields:  Lyric Order  Lyric  You can view the output  here . After you finish the challenge make sure to fill in the  participation tracker , then ...

Solution by: Rosh Khan  You can download the workbook from here Step 1- Input Data  As with all challenges, the data can be downloaded from the challenge requirements. As we are working with Excel files, we can choose Microsoft Excel as our connection type. Step 2 - Split the field ‘Name’ into Game Title and Developer We know that the field ‘Name’ contains two pieces of information: Game Title and Developer, and therefore needs to be separated. The characters separating the two in this case are “ (made by “ , therefore, we can click on More Options under the field ‘Name’ -> Split Value -> Custom Split -> and define our separator to be “ (made by “  and Split off the first 2 fields. Please note: There is a space before ‘)‘ and after ‘by’ After applying the following formatting: Change field names to be ‘Game Title’ and ‘Developer’ Removing trailing and leading spaces from both fields Removing punctuation from the ‘Developer’ field (More Options -> Clean -> R...

Challenge by: Rosh Khan Context This week, we are focusing on an online marketplace for buying and selling used games. The marketplace wants to identify its top performing games by their rating and sales so they can reach out to game developers for potential partnership opportunities. But first, the data needs to be cleaned up! Inputs There are two inputs: 1. Sales Jan-Feb 25 2. GBP Exchange Rates Requirements Input the 2 files The field ‘Name’ contains game titles and their developers. Fix this by creating two fields: ‘Game Title’ and ‘Developer’. The ‘User Review’ field also contains two pieces of information; a star rating out of 5 and any comments left by customers. Extract only the star rating. If no rating is present, keep it as N/A. Keep only 5 star rated games. Connect the GBP Exchange Rates file to the Sales Jan-Feb 25 table and use the ‘Exchange Rate’ field to convert all sales into GBP. Name this field ‘Sales (£)’. Sales currency based on the local currency of where the sale...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Split Nice or Naughty After inputting the data we can then start to split out the names on the Naughty or Nice list and the order in which they are on the list.  First we want to split the List and Order from the File Paths field. We can do this using an automatic split or using a split on the space. We can then rename split 1 to List and split 2 to List Order.  Step 2 - Index & Score Next we want to create an index field by combing the File Path number and id.  Index   ([List Order]*100)+[id] From here we can then calculate a Nice and Naughty Score:  Nice Score {FIXED [first_name]: SUM(IF [List] = 'Nice' THEN 1 ELSE 0 END)} Naughty Score   {FIXED [first_name]: SUM(IF [List] = 'Naughty' THEN 1 ELSE 0 END)} Then finally we can determine what list each name should be on based on their scores. Nice or Naughty?    IF [Nice Score] > [Naughty Score] THEN 'Nice' ELSEIF [Naugh...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Clean Week First we can clean the table so that we remove any rows within the Couple field that are equal to 'Couple'. These are the headers from where the web scraping wasn't quite accurate. We can then focus on the Week field and making sure that it's a numeric field. First we can split the field so that we only have the information from before the first ':'  We can then use this field to remove any punctuation and letters so that we only have a number remaining. We can then remove the other fields, rename this one to Week, and make sure it's a whole number.  Step 2 - Finalists Next we want to filter so that we're only looking at the finalists from each series. First we want to make sure that the 'Runners-Up' values are grouped together and have the same spelling.  Then we want to create a field to identify the finalists:  Finalist   IF  [Result]='Winners' OR  [Res...

 Created by: Carl Allchin Well another year of Preppin' Data comes to an end with a perfect challenge of whether you are on the naughty or nice list? Santa has received a number of files that even he has been able to union together but needs some help to work out whether each person is on the naughty or nice list.  Santa has spotted there is a number of names that have been duplicated as records of niceness and naughty deeds have been submitted throughout the year so can you help him solve who should be in each list based on: If a name is only on the nice or naughty list, then that's what list they are part of.  If a name is on both the naughty and nice list, the number of times they feature on each list will determine whether they are naughty or nice. (Whichever list has the most mentions determines the end result) If there is an even split between naughty and nice mentions, the latest mention will determine which list the person will end up on.  …and there was you ...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Reshape Data Our first task is to combine our two tables so that we have a single table to work from. This involves a few different steps and joins to get the data in the right shape. First we want to input both of the tables and join these together using an inner join where country = entity.  From here we can remove the entity field and rename the Life Expectancy at Birth to Country: Life Expectancy at Birth. We also want to include the continent information, so from the List of Countries input, we can create an aggregate step and group by Continent. This will create a list of all the continents which we can use to join back to our Life Expectancy table. With the second join this time we want to use an inner join where entity = continent:  We can then remove the Entity and Code fields and rename the Life Expectancy at Birth field to Continent: Life Expectancy at Birth.  Now we have both of these tab...