When learning a new skill, it is always important to practice, practice and practice! Thus, I have been using the Preppin' Data Challenges to hone my skills in SQL and Snowflake. These are weekly exercises to help you learn and develop data preparation skills. The challenges are designed for learning Tableau Prep but they can be completed in R, Python, SQL, DBT, EasyMorph and many other tools.
To start, I will be completing challenge 2023- Week 2. This challenge involves constructing International Bank Account Numbers (IBANs). We have all the information in separate fields, we just need to put it altogether in the following order. The link to the challenge is: https://preppindata.blogspot.com/2023/01/2023-week-2-international-bank-account.html
To start, I brought the data in to inspect it, using the select * to bring in all columns from the tables. This challenge has two inputs, but we can join them using the BANK column!
To structure a join, we need to select the first table (transactions) and give it an alias/code "tr". We then select the type of join and other table (swift codes) we want and give that a code "sc". Finally, we say what field we're joining on, with code.field for each table.
To create IBAN codes, we need 5 fields in this order:
The first column we need is a country code. This is a simple string 'GB' which I've named country_code.
The next columns was the check_digits and swift_code.
Next, we need the sort code without the dashes. We use a replace function to replace the '-' with nothing.
Finally, we need the account_number. After that, we use the same join.
We have all the columns we need, but these need to be combined together. Instead of commas separating columns, we use || to combine the columns. We then rename this column to "IBAN".
And that's it! This Preppin' Data Challenge has been useful practice for joins and concatenating columns!