2020: Week 29 - Solution
Solution post by Tom Prowse
Download our full solution here.
The challenge this week looked at refunds and what happens when events get cancelled. In the challenge we looked at creating email lists and also exchange rates.
Step 1 - Join Data
The first step this week is to combine all three of our data tables, into one data source. To do this we can start with joining the Attendee List and the Account Manager tables. Before joining we need to identify which company each of our attendees is from, by extracting the company name from their email address.
To achieve this we can use a couple of splits:
- Custom Split - Everything after the '@' symbol.
- Custom Split - Everything before the first '.'
As a result of this, we should have a list of the company names:
In some other tools, we would need to prepare our able Company list a little further due to the different cases (eg, the first letter being a capitalised) but Tableau Prep recognises this and will join our fields together nicely. We used the following join:
From the challenge post, we want to keep the company name which has a capitalised first letter, so can remove the Company Name field.
Now we have the AM and Attendee list joined, it's now time to join the Exchange Rates table. These won't join together nicely, therefore we need to prepare some of the fields first. From our original workflow, we need to convert the countries from full name to country code by using the following calculation:
Currency
CASE [Country]WHEN 'United States' THEN 'USD'WHEN 'United Kingdom' THEN 'GBP'WHEN 'Canada' THEN 'CAD'WHEN 'Mexico' THEN 'MXN'ELSE 'EUR'END
Then within the Exchange Rates workflow, we need to replace the '-GBP' section for each Currency. We do this by using the following calculation:
Currency
REPLACE([Currency],'-GBP','')
After replacing the '-GBP', we now have the following table for our Exchange Rates:
We now have a common field that we can both of the tables on, however, we are missing the GBP currency, therefore we want to use a outer left join so that we ensure that we are bringing through all of the required fields. The join conditions look like this:
Step 2 - Calculate Ticket Price
Now we have all of the tables into one data source, we can now start to calculate the price of a ticket in each of the local currencies. We can use this calculation for the conversion:
Ticket Price Local
[Ticket Price (£)] * [Rate]
We have now calculated the price of the tickets in each of the countries, apart from the UK. Within the UK price, we are still left with a Null value. There are a couple of ways that we can overcome this, including replacing the null value, or using a calculation to replace the null with the ticket price. However, we thought the easiest way was to Merge the Ticket Price Local and the Ticket Price field. This will replace any Null values with the correct ticket price.
Before the merge:
After Merge:
We now have the required ticket price for each different country/currency.
Step 3 - Output 1
We are now ready to create both of our outputs. First, we want to start with Output 1, and we need to sort our AMs in order of who has the most clients on the list.
To do this, we can use the Rank functionality, to rank our clients in the following order:
This allows us to get our first output:
Step 4 - Output 2
The first step we need to take for our 2nd output is to calculate how much money we have gained/lost from the event. As there are different options for attendees in how to process their refund, we can use the following calculation to calculate whether we have gained or lost money from each attendee:
Money Gain/Loss
CASE [Refund Type]WHEN 'Full Refund' THEN -[Ticket Price Local]ELSE [Ticket Price Local]END
Next we want to calculate the total by currency and country. To do this we use an aggregation tool with the following setup:
After the aggregation, we are then left with our required 2nd output:
Week 29 complete! Both of the full outputs can be found here.
Make sure to fill in the participation tracker, share using #PreppinData on Twitter and post you solutions onto our Tableau Forums community page so that we can compare our workflows!
