2023: Week 7 - Solution
Solution by Tom Prowse and you can download the full workflow here.
We are starting to move into some trickier challenges during the Intermediate month... this time looking at flagging fraudulent suspicions by combining multiple data sets and flagging transactions based on given rules.
Step 1 - Transaction Path & Detail
First we want to input the transaction path table. This will become our first input but we need to make some changes so that we ensure that it matches the other tables.
After inputting, we can update the names of the fields so to remove the _ from any field names. Luckily there aren't too many fields in this table, so we could rename them individually, but we could also utilise the 'Rename Fields' functionality in Tableau Prep.
From here we can select the fields that we want to rename, and then also define what we want to replace within the field name. In our case we want to select Account_From and Account_To, then replace the '_' with a ' ':
We can then input our 2nd table - 'Transaction Detail'. This contains information about each of the transactions and also contains information about transactions that have been cancelled so we can remove any 'Y' values from the Cancelled field then remove the cancelled field from the table.
Once the table is filtered we can combine this with the Transaction Path table using an inner join on Transaction ID:
The final step that we need to take to this table is to filter the value to transactions greater than £1,000 as stated in the rules in the requirements. We can use a range filter where 1,000 is the minimum:
After filtering, our table should now look like this:
Step 2 - Account Information
Next we want to input the Account Information table into our workflow. Again there are a few steps that we need to take before being about to combine this table.
First, we need to clean that Account Holder ID field as some rows contain multiple IDs. To do this we can split the field using the ',' as a separator:
We can then remove the original Account Holder ID field, and pivot the split fields using a columns to rows pivot:
This will give us a single row for each Account Holder ID, but will also create a lot of empty rows that can be removed. After the pivot the table should look like this:
Step 3 - Account Holders & Account Information
'0'+[Contact Number]
After this we can combine with the Account Information table using an inner join on Account Holder ID:
We can then tidy the fields by removing any duplicates or ones we no longer require so that table looks like this:
Step 4 - Bringing it all together
We can now start to combine both of the workflows to make a single table. We can combine them using an inner join where Account From = Account Number:
Once both workflows are combined we can make sure that all the rules have been considered by removing the 'Platinum' account from the Account Type field. Once these have been removed our table is ready to output and should look like this:
