Page tree



Contents:

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

This example explores how you can use aggregation functions to calculate rank of values in a column using the KTHLARGEST and KTHLARGESTUNIQUE functions.

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.

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

This page has no comments.