2021: Week 33 - Solution

 

Solution by Tom Prowse and you can download the workflow here.

This week we continued with our Excel themed challenges by looking at how we could add some additional rows within Tableau Prep. In Excel this is easily done, but it takes some slightly different logic to achieve this in Prep so let's see how it can be done!

Step 1 - Create One Data Set

The first step is to create a single data source which includes all of the different files within the Input folder. Luckily these all have the same structure, therefore we can use the Wildcard Union to stack all of these on top of each other. 

We want to bring through all of the files, so we use the * matching pattern to include all files

After this we can remove the 'File Path' field, change the 'Table Names' to a date, then rename it to 'Reporting Date'. Finally, we are going to need the Reporting Date field twice in future steps so we can duplicate this field as well. 

Our table should now look like this: 

Step 2 - Min & Max Dates

Next we need to find the Min and Max dates where each order appeared. We can use an aggregate tool to do this by grouping by Orders & Sale Date and then finding the Min and Max of the Reporting Date.

From here we can then add 1 week to the max date to show when the order was fulfilled by, using the following calculation:

Fulfilled Date 
DATEADD('week',1,[Max Reporting Date])

Step 3 - New Order or Fulfilled Logic

In this stage we want to classify each of the orders to see if they are a new order or they have been fulfilled. First we can focus on the New Orders, by joining our Min & Max dates back to the original data set by joining on Orders:

From here we can remove any duplicated fields and where the order is New or Unfulfilled by using the following calculation: 

Order Status 

IF [Min Reporting Date] = [Reporting Date] THEN 'New Order' 

ELSE 'Unfulfilled Order' 

END 

This helps us to identify when the first date of each order is, and therefore classify these as New Orders.

Our table should now look like this: 

We also need to calculate the status of the fulfilled orders. To do this we want to go back to the step where we added 1 week to our reporting dates, and then add a new string calculation of 'Fulfilled'. This will classify all of these orders as 'Fulfilled'. 

Now we can bring both of these branches together into a single table by using a union tool. This will stack both tables on top of each other and combine them together. The combined table should look like this: 

Step 4 - Remove Rows

The final step is to remove any of the rows that don't match the logic in the requirements. To do this we want to find out the max reporting date for each order. This can be calculated with a fixed LOD calculation: 

Max Report Date

From here we can then calculate whether the order is actually unfilled or not, buy using the following calculation: 

Actually Unfulfilled? 
IF [Max reporting date] = [Reporting Date] AND [Order Status] = 'Fulfilled' THEN 'Delete' ELSE 'Retain' END 

This helps to provide the correct status based on the logic in the requirements, and then we can filter the 'Delete' rows from our table so we have the final output that looks like this: 

The full output can be found here. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!