2019: Week 23 Solution

You can view our full solution workflow below and download it here!

Our full workflow solution.

Wildcard Union.

Importing the data

The very first requirement is to import all the sheets from the input data. The quickest way to do this is to simply drag one of the sheets from the connections pane onto the canvas and select the 'Wildcard Union' option. By default this option will include all sheets within an Excel file and union them together!

We want to remove the [File Paths] field but leave the [Table Names] field as it contains date information that we'll need later when calculating the actual dates for each row of data.

Calculating the dates

There's two stages to calculating the exact dates here:

1. Stripping the 'week commencing' date from the [Table Names] field.
2. Converting the [Day] field to something that we can add to the 'week commencing' date.

Stripping the WC date

Getting the [Week Commencing] date.

There's a vast variety of ways of stripping and forming the date from the table names field. The screenshot to the right demonstrates a sample of what I believe to be some of the most effective ones. These methods are summarised below.

1. The "Replace" method used REPLACE() "Dave's Sales wc " with nothing and then adds " 2019" onto the end.
2. The "RegEx" method uses REGEX_EXTRACT() to find everything after the first number character and then adds " 2019" onto the end.
3. The "Split" method uses SPLIT() to pull out the text after the penultimate space character and last space character and then adds " 2019" onto the end.
4. The "Mid & FindNth" method uses FINDNTH() to find where the 3rd space character occurs, then uses MID() to pull out all the text after this character, and then adds " 2019" onto the end.

After any one of these methods you should be left with something like "15th Jul 2019". From here you can simply change the data type to DATE and Prep will convert all the strings into actual dates for you. If you want to manually use a date function yourself, you'll first need to strip out the day suffix (i.e. st, th, rd, nd).

Adding the weekday

We now have the [Week Commencing] date, so we need to add the day of the week in order the get the actual [Date] for each row. I think the simplest way is to first use a CASE statement to convert [Day] to a number that represents the day of the week and then use DATEADD() to add the appropriate number of days to [Week Commencing].

[Day]	[Date]
CASE LOWER(LEFT([Day],2))     WHEN 'mo' THEN 0     WHEN 'tu' THEN 1     WHEN 'we' THEN 2     WHEN 'th' THEN 3     WHEN 'fr' THEN 4     WHEN 'sa' THEN 5     WHEN 'su' THEN 6 END	DATE(     DATEADD(         'day'         ,[Day]         ,[Week Commencing]     ) )

Creating the Title Case fields

Unfortunately there's no titlecase function in Tableau so we'll need to form these titlecase fields ourselves. I believe the quickest way in the case is as follows:

1. Make the [Notes] field lowercase by using the LOWER() function.
2. Use a custom split to split the [Notes] field on every space.
3. For each required word in each required field, use the following calculation:
   
   UPPER( LEFT( [Note – Split X], 1 ) ) + MID( [Note – Split X], 2 ) 

This will make the first letter of the word uppercase and then attach everything after the first letter to the end to make the required titlecase word!