Page tree



Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by the number of tie values.
  • Rank values start at 1 and increment.

  • Ranking order varies depending on the data type of the source data. For more information, see Sort Order.

  • You must use the group and order parameters to define the groups of records and the ordering column to which this transform is applied. 

  • This function works with the following transforms:
  • This function assigns ranking values to match the total number of rows in a group. For fewer discrete ranking values when ties are present, see DENSERANK Function.

Basic Usage

window value:RANK() order:MySales group:Salesman

Output: Generates the new column, which contains the ranking of mySales, grouped by the Salesman column.

Syntax

window value:RANK() order: order_col group: group_col

For more information on the order and group parameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Notes.

Examples

Example - Rank Functions

This example demonstrates the following two functions:

  • RANK - Generates a ranked order of values, ranked within a group. 
    • If there are three tie values in a group, the next ranking is three more than the tie values.
    • See RANK Function.
  • DENSERANK - Generates a ranked order of values, ranked within a group.
    • If there are three tie values in a group, the next ranking is one more than the tie values.
    • See DENSERANK Function.

Source:

The following dataset contains lap times for three racers in a four-lap race. Note that for some racers, there are tie values for lap times.

RunnerLapTime
Dave172.2
Dave273.31
Dave372.2
Dave470.85
Mark171.73
Mark271.73
Mark372.99
Mark470.63
Tom174.43
Tom270.71
Tom371.02
Tom472.98


Transform:

You can apply the RANK() function to the Time column, grouped by individual runner:

window value: RANK() group: Runner order: Time

You can use the DENSERANK() function on the same column, grouping by runner:

window value: DENSERANK() group: Runner order: Time

Results:

After renaming the columns, you have the following output:

RunnerLapTimeRankRank-Dense
Mark470.6311
Mark171.7322
Mark271.7322
Mark372.9943
Tom270.7111
Tom371.0222
Tom472.9833
Tom174.4344
Dave470.8511
Dave172.222
Dave372.222
Dave273.3143


 

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 3 rates

This page has no comments.