2023: Week 9 - Solution

Solution by Tom Prowse and you can download the workflow here. 

This is the first week of our advanced week! Building on the previous 2 months we're looking to extend our knowledge even further and look into some of the techniques that are a little harder to complete.

For the challenge we want to identify what the bank balance for a customer is after each transaction and to create a bank statement. 

Please note that there have been some problems with this workflow in Tableau 2022.4, so you may need to go to an older version if you are getting unexpected errors. 

Step 1 - Combine Transaction Details & Path

First we want to input the Transaction Path and Transaction Details tables into the workflow. For the Transaction Path we want to replace any '_' with a space. We can utilise the Rename Fields functionality for this:

For the Transaction Details we want to filter to exclude 'Y' from the 'Cancelled?' field and then remove the 'Cancelled?' field. 

We are then ready to join our tables together using an inner join on Transaction ID. 

Our table should now look like this: 

Step 2 - Transactions In & Out

We now need to split the transactions into incomings and outgoings. For this we want to create two separate branches - 1, Incoming Transactions where we remove the Account From field, 2, Outgoing Transactions where we remove the Account To field and also make the values negative by multiplying by -1.

Value 

(-1)*[Value]

We can then include the final input, Account Information, into the workflow as a third branch. Within the input we only need to keep the Account Number, Balance Date, and Balance fields. 

After this we can bring all of these back together with a union so we have three fields - Account Number, Balance Date, & Balance: 

Step 3 - Transaction Order

Next we want to identify the order in which the transactions occurred so that we can then start to calculate the balance at each stage.

To order the transactions we can use a Rank calculation where we group by Account Number, and rank the Balance Date (Ascending) and Balance (Descending): 

At this stage we can rename the Value field to Transaction Value. 

Now we need to calculate a running sum based on the balance at each stage and account. To do this we want to create a self-join by creating a new clean step then remove the Balance Date field. 

From here we can join the clean step with the previous clean step using an inner join where Account Number = Account Number and Transaction Order >= Transaction Order (Self-Join)

This will massively increase the number of rows within our data set as it has joined lots of different combinations based on the join conditions.

Due to those extra combinations we need to aggregate the table to calculate the running sum. In the aggregation we want to group by Transaction Order, Account Number, Balance Date, and Transaction Value and then Sum the Transaction Value-1 field. 

After the aggregation we just need to tidy some of the fields up with some calculations: 

Balance 

ROUND([Transaction Value-1],2)

This will round the balance field to 2 decimal places. 

We can then make sure that the opening transaction value is null as per the requirements: 

Transaction Value 

IF [Transaction Order]=1 THEN NULL

ELSE [Transaction Value]

END

We can then remove the Transaction Order and Transaction Value-1 fields and we are ready to output.

You can download all the output from here

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 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!