Page tree

NOTE:  Trifacta Wrangler is a free product with limitations on its features. Some features in the documentation do not apply to this product edition. See Product Limitations.

   

This example explores how you can use aggregation functions to calculate rank of values in a column.

Functions:

ItemDescription
KTHLARGEST Function Extracts the ranked value from the values in a column, where k=1 returns the maximum value. The value for k must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.
KTHLARGESTUNIQUE Function Extracts the ranked unique value from the values in a column, where k=1 returns the maximum value. The value for k must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.

Source:

You have a set of student test scores:

StudentScore
Anna84
Ben71
Caleb76
Danielle87
Evan85
Faith92
Gabe87
Hannah99
Ian73
Jane68

Transformation:

You can use the following transformations to extract the 1st through 4th-ranked scores on the test:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula KTHLARGEST(Score, 1)
Parameter: New column name '1st'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula KTHLARGEST(Score, 2)
Parameter: New column name '2nd'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula KTHLARGEST(Score, 3)
Parameter: New column name '3rd'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula KTHLARGEST(Score, 4)
Parameter: New column name '4th'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula KTHLARGESTUNIQUE(Score, 3)
Parameter: New column name '3rdUnique'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula KTHLARGESTUNIQUE(Score, 4)
Parameter: New column name '4thUnique'

Results:

When you reorganize the columns, the dataset might look like the following:

StudentScore1st2nd3rd4th3rdUnique4thUnique
Anna84999287878785
Ben71999287878785
Caleb76999287878785
Danielle87999287878785
Evan85999287878785
Faith92999287878785
Gabe87999287878785
Hannah99999287878785
Ian73999287878785
Jane68999287878785

Notes:

  • The value 87 is both the third and fourth scores.
    • For the KTHLARGEST function, it is the output for the third and fourth ranking.
    • For the KTHLARGESTUNIQUE function, it is the output for the third ranking only.

This page has no comments.