Preppin' Data

 Challenge By: Jenny Martin Data preppin' ideas really are all around! I was hanging some pictures the other week and I had some weird and wonderful sizes, so working out the perfect frames was a bit tiresome. If only I could use a data preppin' tool to speed up the process...  Inputs We have 2 inputs this week: Picture sizes  Frame sizes    Requirements Input the data Split up the sizes of the pictures and the frames into lengths and widths Remember an inch is 2.54cm Frames can always be rotated, so make sure you know which is the min/max side See which pictures fit into which frames Work out the area of the frame vs the area of the picture and choose the frame with the smallest excess Output the data Output 4 fields Picture Frame Max Side Min Side 14 rows (15 including headers) After you finish the challenge make sure to fill in the  participation tracker , then share your solution on Twitter using  #PreppinData  and tagging  @Datajedininja...

Challenge by: Jenny Martin This week we're looking at a slightly odd way that Chin & Beard Suds Co have been structuring their store sales and target data:   As you can see, for each Store, there are 3 rows, the first being the Sales values, the second row containing the Target values for each month and the third row containing the difference between these values. It's your job to transform this monstrosity  unique table into a more conventional output that we could use in Tableau Desktop.  Inputs You have 2 options this week: Start the challenge with a Row ID already present (as pictured above) Use this as an opportunity to play with the Script step and create your own Row ID! Our solution will cover the RServe option, but you're welcome to use TabPy instead. For help getting set up with RServe, check out this blog For TabPy, check out this blog by our colleague Brian Scally   Requirements Input the data Make sure Store names have filled down correctly and remo...

You can view our full solution workflow below and download it here ! Our full solution workflow. 1. Use a Running Total to calculate the total stock in the warehouse to date. Starting with out output from week 34, we first want to calculate the total stock in our warehouse for each [Product] and [Scent] on any given date. To do this, we first duplicate the data and then join it to itself on: [Product] = [Product] [Scent] = [Scent] [Date] >= [Date] This means each row of data gets every previous row joined to it if it has the same product and scent. From this point, we can use an aggregation step to complete our running total and find our total quantity requested to date: GROUP on: [Supplier] [Date] [Product] [Scent] [Quantity] SUM up [Quantity-1] and rename to [Total Quantity to Date]. Total stock in warehouse aggregation settings. 2. Use a Running Total to calculate the total stock requested to date. We now need to perform another running total on t...