EXAMPLE - KTHLARGEST Function

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

Source:

You have a set of student test scores:

StudentScore
Anna84
Ben71
Caleb76
Danielle87
Evan85
Faith92
Gabe87
Hannah99
Ian73
Jane68

Transform:

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

derive value:KTHLARGEST(Score, 1) as: '1st'

derive value:KTHLARGEST(Score, 2) as: '2nd'

derive value:KTHLARGEST(Score, 3) as: '3rd'

derive value:KTHLARGEST(Score, 4) as: '4th'

Results:

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

StudentScore1st2nd3rd4th
Anna8499928787
Ben7199928787
Caleb7699928787
Danielle8799928787
Evan8599928787
Faith9299928787
Gabe8799928787
Hannah9999928787
Ian7399928787
Jane6899928787

Notes:

  • Since the value 87 is both the third and fourth scores, it is listed twice as the result of separate transform steps.
Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation