2023: Week 2 - Solution

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

For the 2nd week of our beginner month throughout January, we looked at creating IBANs for our fictional Bank.

Step 1 - Sort Code

First we want to connect to our data source and then bring in the Transactions table. From this table we want to remove the '-' between the digits of the 6 digit sort code. 

There are various ways that we can do this, but Tableau Prep makes it really easy by having a 'Remove Punctuation' feature within the clean menu. 

All we need to do is click on the three dot menu, then clean, then Remove Punctuation: 

As a result the '-' is removed and all we're left with is the 6 digits.

Step 2 - Swift Code Lookup

Up next we want to include another table, this time from the Swift Codes input. 

From here we can combine our original workflow with the Swift Codes by joining them together. By using a join we are placing the tables side by side and using a field to match the appropriate rows.

Within this join we can use the Bank field from both tables within an inner join to combine. 

After removing the duplicated Bank field our table should look like this: 

Step 3 - Create IBAN

Now we have the fields from both tables combined together, we are almost ready to create the IBAN field. 

The IBAN has the following structure: 

Country Code + Check Digits + Bank Code + Sort Code + Account Number

We have all of these fields, apart from the Country Code. We can create this using a string calculation, and because they are all from the UK we can just use 'GB' within a calculated field.

To create the rest of the IBAN, we can concatenate the rest of the fields using this calculation: 

IBAN 

[Country Code]

+

[Check Digits]

+

[SWIFT code]

+

[Sort Code]

+ 

[Account Number]

We need to ensure that all of the field types are a string, or else we will get an error.

Now we have the IBAN created, we can keep just the IBAN and Transaction ID as our fields that we want to output:

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