2020: Week 10 Solution

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

Our full workflow solution.

This week we're breaking the workflow up into 3 main chunks:

If you're starting with the pre-prepared data then jump to section 2 to begin applying the clue logic.

1. Reshaping the data

Because the rows in our initial input don't actually have any meaning, we need to break them and put them back together. We don't actually know how they should go back together however, so we're going to generate every possible combination of [Title], [LastName], [Product], and [Priority].

We generate all possible combinations by first splitting each field into it's own step and then joining them all back together.

1.1 Split up the fields.

Start by creating a dummy join field. This is the field we'll use in our join conditions so that every value in a field can join to every other value in a field. It can contain anything, though a numeric [Dummy] field may result in slightly quicker processing, hence why we use the number 1.

The dummy field calculation.

Next, create four different branches of clean steps. In each branch, remove all the fields apart from the [Dummy] field and one of the other fields (a different field for each branch).

One of the four branches.

1.2 Generate all possible combinations.

Join all these branches back together one after the other. They should automatically join on [Dummy] = [Dummy] so you don't actually need to configure anything.

Joining all the fields to create all possible combinations.

Afterwards, remove all the [Dummy] fields and you should be left with a 256 row data set which includes every combination of [Title], [LastName], [Product], and [Priority].

2. Applying the clue logic

Throughout the entirety of the rest of this workflow, we will just be filtering down the data using the profile pane with the exception of a single typed filter and only using exclude filters.

We'll apply the logic of each clue one at a time and in order for our solution. The filters being applied are bullet-pointed so you can more easily read them. The number of rows left after each filter is included at the end of each bullet points in brackets so we can track our journey from (256R) to (4R)

2.1 Clue 1

Only the customer with the highest priority has a title and last name that begin with the same letter.

Here is where we need to write a little custom filter.

Adding the custom filter.

After clicking "Filter Values..." at the top of the profile pane we can use the LEFT() function to test whether [Title] and [LastName] share an initial and whether the [Priority] is 1. (160R)

(

[Priority] = 1

AND

LEFT([Title],1) = LEFT([LastName],1)

)

(

[Priority] != 1

AND

LEFT([Title],1) != LEFT([LastName],1)

)

The first AND statement keeps rows where the [Priority] is 1 and [Title] and [LastName] share an initial. Conversely, the second AND statement only keeps rows with a lower priority if [Title] and [LastName] do not share an initial.

This removes nearly 100 rows right away.

2.2 Clue 2

Bevens' priority is directly after Dimmadome.

Neither of these people ordered the Chamomile Bar or the Hibiscus Soap-on-a-Rope.

The first part of this clue means that Bevens cannot be priority 1 and Dimmadome cannot be priority 4. If Bevens was priority 1 then he could not be directly after Dimmadome and if Dimmadome was priority 4 then he could not come before Bevens.

Select "Bevens" and "1" in the profile pane and click exclude. (156R)
Select "Dimmadome" and "4" in the profile pane and click exclude. (144R)

The second statement means two things:

Firstly, that Dimmadome and Bevens together must have ordered the Lemon Gel and the Rose Bar since they did not order the Hibiscus SOAR and the Chamomile Bar.

Secondly, that Shadwell & Rotzenheimer must have ordered the Hibiscus SOAR and the Chamomile Bar since the Lemon Gel and Rose bar must have been ordered by Dimmadome and Bevens.

Select "Hibiscus Soap-on-a-Rope", "Chamomile Bar", "Bevens", and "Dimmadome" in the profile pane and click exclude. (112R)

Select "Rose Bar", "Lemon Gel", "Shadwell", & ""Rotzenheimer" in the profile pane and click exclude. (72R)

In just two clues we've gone from 256 rows to just 72!

2.3 Clue 3

The Sergeant and the person who ordered Lemon Gel are either 1st priority or 3rd priority.

This clue means three things:

Firstly, that the Sergeant is 1st or 3rd priority.

Secondly, that the Lemon Gel is 1st or 3rd priority too.

Thirdly, that also implies that the Sergeant did not order the Lemon Gel

Select "Sergeant", "2", & "4" in the profile pane and click exclude. (62R)
Select "Lemon Gel", "2", & "4" in the profile pane and click exclude. (56R)
Select "Sergeant" and "Lemon Gel" in the profile pane and click exclude. (54R)

We're now down to just 54 rows. From this point the number of rows reduced by each clue is greatly reduced as we start to zone in on the correct 4 rows.

2.4 Clue 4

The Reverend didn't order the Rose Bar and isn't 2nd priority.

This one is fairly straightforward and there isn't any extra information to glean.

Select "Reverend" and "Rose Bar" in the profile pane and click exclude. (49R)
Select "Reverend" and "2" in the profile pane and click exclude. (47R)

We're now down to 47 rows.

2.5 Clue 5

The Sergeant either ordered Hibiscus Soap-on-a-Rope or is 4th priority.

After applying the previous clues, clicking on "Sergeant" in the profile pane reveals that he can't actually be 4th priority anymore. This means two things:

Firstly, must have ordered the Hibiscus SOAR.

Secondly, this then means that no one else can have ordered the Hibiscus SOAR since each order is unique.

Select "Sergeant", "Rose Bar", & "Chamomile Bar" in the profile pane and click exclude. (43R)
Select "Hibiscus Soap-on-a-Rope", "Doctor", "Baroness", & "Reverend" in the profile pane and click exclude. (28R)

We're now down to 28 rows.

2.6 Clue 6

The priority of the person who ordered the Rose Bar is directly after the person who ordered the Lemon Gel.

This clue is similar to clue 2: Rose Bar can't be 1st priority and Lemon Gel can't be 4th priority. However, clicking on "Lemon Gel" in the profile pane reveals that Lemon Gel can only be priority 1 or 3 at this point. This means that Rose Bar must be either priority 2 or priority 4 as it has to come directly after the Lemon Gel.

Select "Rose Bar", "1", & "3" in the profile pane and click exclude. (25R)

We're now down to 25 rows.

2.7 Clue 7

Dimmadome is not a Doctor and the Baroness didn't order the Hibiscus Soap-on-a-Rope.

From Clue 5 we've actually already excluded the possibility that the Baroness ordered the Hibiscus SOAR, however we do have to exclude the possibility that Dimmadome is the Doctor.

Select "Dimmadome" and "Doctor" in the profile pane and click exclude. (24R)

We're now left with just 24 rows. From this point onward, we need to revisit the previous clues as well as explore the profile pane to whittle these last 24 rows down to just 4.

3. Applying the remaining logic.

In this section, it is required to explore the data in the profile pane whilst keeping the above clues in mind. The following gives the order that we applied filters in order to discover the final correct 4 rows.

The lemon gel must be 3rd priority.

The Lemon Gel can only be 3rd priority now according to the profile pane. This means nothing else can be 3rd priority.

Select "3", "Chamomile Bar, & "Hibiscus Soap-on-a-Rope" in the profile pane and click exclude. (18R)

The rose bar can't be 2nd priority.

We also know from Clue 6 that Rose Bar must be one priority after the Lemon Gel, so we now know that Rose Bar is in 4th priority.

Select "Rose Bar" and "2" in the profile pane and click exclude. (16R)
Select "4" and "Chamomile Bar" in the profile pane click exclude. (11R)

The hibiscus SOAR must be Sergeant Shadwell in 1st priority.

Clicking on the Hibiscus SOAR or the Sergeant now reveals that one order must be Sergeant Shadwell in 1st priority. We can now filter the [LastName] and [Priority] fields to reflect this.

Select "1" and "Reverend" in the profile pane and click exclude. (10R)
Select "Shadwell", "Baroness", & "Doctor" in the profile pane and click exclude. (8R)

The rose bar must be Doctor Bevens in 4th priority.

Clicking on Rose Bar or priority 4 now reveals that another order must be the Rose Bar by Doctor Bevens in priority 4. We can now filter the [LastName] and [Title] fields to reflect this.

Select "Bevens" and "Lemon Gel" in the profile pane and click exclude. (6R)
Select "Doctor" and "Rotzenheimer" in the profile pane and click exclude. (5R)

Rotzenheimer must be Baroness Rotzenheimer with the Chamomile Bar in 2nd Priority.

Finally, we can see the last two orders must be the Chamomile Bar by Baroness Rotzenheimer in 2nd priority and the Lemon Gel by Reverend Dimmadome in 3rd priority.

Select "Baroness" and "Dimmadome" and click exclude. (4R)

We now have our complete and accurate order list!

The final data revealed.

Search This Blog

Preppin' Data