This week was an exciting challenge – data scaffolding! When I first heard of this, it went right over my head. But actually, it’s a really clever way of appending one data source to another – definitely worth knowing!
Check out the challenge here.
So why go through all this hassle? What’s the point of it?
Well, it allows us to see what our expenditure is every month. We can see all the overlapping contract periods, resulting in higher monthly costs, to the point where we are only paying £20 a month from late 2019.
This is great for planning and budgeting.
Let’s get on with it!
When I append data, I typically just use Alteryx, since there is a tool that specifically allows you to do this and is very easy. I would never have thought to even try it in Tableau or now, Tableau Prep! But, data scaffolding “got you covered”!
This is my solution.
First, I opened each data source to see what was inside it. From then I knew that I needed to append the field “length” from the data source “Scaffold” to each person in the data source “Contract Details”.
To append, I created a calculated field in each data source called “1” with 1 as the formula, essentially creating an ID of “1” for each row of data, looking something like this:
I then joined both of them, linking them on the newly calculated field “1”. This essentially added all the rows in the “length” field to every row in the other data source.
From there, I filtered out all the unnecessary months by using the condition:
length <= months
This resulting in a True/False boolean. I filtered out all the False rows.
Next, create the payment date. This was a simple dateadd calculation:
From there, I had my data ready to go in Tableau Desktop, creating this final viz below: