2023: Week 1 - Solution

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

It's the first solution post of the year! If you are new to #Preppin' Data then this is where we recap the challenge from the previous week and walk you through how we went about solving it. Each week we will have the blog post and video solution to help you get through the challenge. These might differ slightly but hopefully that will show that there isn't always a 'right' answer but there are many ways of getting to the desired outcome.

Let's get started with the solution!

Step 1 - Split Transaction Code

First up we want to connect to our data source. We are working with a CSV file (text input) that contains all of the transaction information from our customers, including a transaction code that we can split apart to get some useful information.

To split the transaction code, we can use the in-built split functionality within Tableau Prep. After creating a clean step, after the input, we can select Transaction Code, click the 3 dot menu, then Split Values, and Custom Split.

From here we want to use the '-' as a separator and split off the first set of letters from the string. This contains the bank information so we just want to split off the first 1 fields: 

We can then rename that field to Bank and our table should look like this: 

Step 2 - Online or In-Person

Next we want to translate the 1 and 0's in the Online or In-Person field. As per the requirements, we want that 1 = Online and 0 = In-Person. 

There are a couple of ways to do this, but probably the easiest way is change the Online or In-Person field to a string. You can do this by clicking on the # then selecting String

Now we can update the values in the string to match the corresponding values by double-clicking on each within the Profile pane: 

Step 3 - Day of the Week

Within the same step we now want to change our date field to just the day of the week. Again, there are a couple of different ways to do this, but we can utilise the in-built functionality within Tableau Prep to help.

On the Transaction Date field, we can click on the 3 dot menu, then select Convert Dates --> Day of the Week 

This will result in our Transaction Date field being changed into a day of the week:

Step 4 - Levels of Detail

The final step this week is to create 3 different tables that contain data at different levels of aggregation. For this we want to use three aggregation tools on separate branches.

Output 1 - Total Values of Transactions by Each Bank

For this we want to Group by Bank and Sum Value: 

This creates our first output: 

Output 2 - Total Values by Bank, Day of the Week and Type of Transaction (Online or In-Person)

This time we want to Group By Bank, Online or In-Person, and Transaction Date, then Sum Value:

This results in output 2: 

Output 3 - Total Values by Bank and Customer Code

The final aggregation is Grouped By Bank & Customer Code, then Sum Value: 

This creates the required table for output 3: 

You can download all the outputs 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!