2020: Week 35
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 remove nulls
- Pivot to create a Month column
- In the most dynamic way possible, assign each row a description of either Sales, Target or Difference
- Hint: the Sales value will have the lowest Row ID value for each Store
- Hint Hint: how can string calculations mid and findnth help you to extract the relevant value?
- Reshape the data so that there is one row for each Store for each Month
- Change the Month field to a Date data type (these months are all from 2020)
- Output the Data
Output
- 5 fields
- Store
- Month
- Sales
- Target
- Difference
- 49 rows (50 including headers)
Here is the Output file to let you check your structure.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14, @JonathanAllenby & @TomProwse1
You can also post your solution on the brand new Tableau Forum where we have a Preppin Data community page. Post your solutions and ask questions if you need any help!
