## Ranking in Tableau Prep Builder

by Ben Wells

Unfortunately, there is no simple tool to rank values in Tableau Prep Builder but the Preppinâ€™ Data Week 16 solution makes this all possible!

I have taken data of the life spans of animals and put this into an excel and opened this in Tableau Prep to try and rank animals on the length of their life spans using the workflow below.

Step 1: Load the data into Tableau Prep

Step 2: Add a step just to duplicate the data

Step 3: Inner join the original and duplicate data from step 1 and 2 by the area of interest with a less than or equal to to rank the highest as first. In this example:

Life Span <= Life Span

E.g. as Turtle (Box) has a life span of 123 years, this will join with all other animals with a life span more than or equal to 123 years. As the turtle has the longest life span this will only join Turtle (Box) to Turtle (Box) ( 123 <= 123)

The Eagle (Golden) has the second longest life span so will join:

Eagle (Golden) to Eagle Golden: 80 <= 123
Eagle (Golden) to Turtle (Box): 80 <= 80

Step 4: As step 3 created many new rows of data, the next step is to create an aggregation and group by [Animal] to SUM[Number of Rows] and MIN[Life Span]. This will sum the number of rows for each animal and will act as the rank field. E.g. Eagle (Golden) has 2 rows so the SUM[Number of Rows] = 2 which is second rank for life span. MIN[Life Span] adds the original column of the life span years (we do not sum due to the duplicated rows caused in step 3).

Success! With a bit of cleaning of the duplicate columns and names we can now see the animal life spans ranked!

Ben Wells