Tableau Prep: Week 007

This week 007 post is about how to use Tableau Prep to manipulate the below two tables,

Ship ID	Departure Date	Max Weight	Max Volume
Tug-05	3/27/2019	500	50
Freighter-04	3/31/2019	5000	500
Freighter-03	4/1/2019	5000	500
Freighter-01	4/6/2019	5000	500
Tanker-02	4/7/2019	1000	200

Salesperson	Departure ID	Date Logged	Product Type	Weight Allocated	Volume Allocated
Daisy Steiner	Tanker-05-04-05-2019	2/21/2019	Spurs Jerseys	111	10
Tim Bisley	Freighter-01-18-06-2019	1/14/2019	Phone Catalogues	756	48
Daisy Steiner	Tanker-04-27-04-2019	1/26/2019	Oil	220	28
Colin Chien	Freighter-02-10-04-2019	3/11/2019	Rubber Ducks	536	20
Colin Chien	Tanker-05-20-05-2019	1/27/2019	Vinyl Records	148	30

to the below formatted table.

Ship ID	Departure Date	Max Weight	Max Volume	Weight Allocated	Volume Allocated	Max Weight Exceeded?	Max Volume Exceeded?
Tug-04	6/11/2019	500	50	304	41	FALSE	FALSE
Tug-04	6/21/2019	500	50	37	16	FALSE	FALSE
Freighter-01	6/18/2019	5000	500	4580	556	FALSE	TRUE
Freighter-02	5/26/2019	5000	500	4252	294	FALSE	FALSE
Tug-01	6/13/2019	500	50	270	44	FALSE	FALSE

On the input file, there are two tabs, [Departure Details] and[Allocation Details].

The idea of this exercise is to:

• Combine these two tables [Departure Details] and the aggregate from this [Allocation Details] together into one table using inner join
• Ascertain whether the Weight Allocated is greater than Max Weight or not
• Ascertain whether the Volume Allocated is greater than Max Volume or not

As shown on the below image, here are the steps to create the above output file.

Below are the steps to create the above table.

1. Read the input file by drag and drop this table [Allocation Details]
2. Add another step for data cleaning
   • • Click on departure ID and select custom split
       • On the Use the separator, enter a space
       • On Slip off, select All
     • Create the [Ship ID] field with this formula
       • Trim([Departure ID – Split 1] +”-” +[Departure ID – Split 2])
       • This is to create the Ship ID for joining purpose
     • Change the type to string for the following fields
       • [Departure ID -Split 3]
       • [Departure ID -Split 4]
       • [Departure ID -Split 5]
     • Create the [Departure Date] field with this formula
       • MAKEDATE([Departure ID – Split 5], [Departure ID – Split 4], [Departure ID – Split 3])
       • This is to create  for joining purpose
   • Add another step to delete the following fields
     • [Departure ID -Split 1] to [Departure ID -Split 5]
     • [Departure ID], [Salesperson], [Date logged], [Product Type]
   • Add Aggregate
     • Drag [Departure Date] and [Ship ID] to Group Fields
     • Drag [Weight Allocated] and [Volume Allocated] to Aggregate Fields
     • The purpose to  aggregate the [Weight Allocated] and [Volume Allocated] by [Departure Date] and [Ship ID]
   • Drag and drop [Departure Detail] tab to the canvas
   •  Drag the [Departure Date] table to join the [Aggregate 1]
     • On the Applied Join Clauses, inner join [Ship ID] to [Ship ID] and [Department ID] to [Department ID] for both
   •  Add another step
     •  Create these two condition formulas
       • Max Weight Exceeded?
         • [Weight Allocated] > [Max Weight]
       • Max Volume Exceeded?
         • [Volume Allocated] > [Max Volume]
       • These two fields are just to test whether the Weight and Volume are exceeded or not
   • Add another step
     •  Delete two fields
       • [Ship ID – 1] and [Departure Date-1]
3. Finally, add the output step to save it as csv.

Attached is the Week 007 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.