## Key

• This line was removed.
• Formatting was changed.
Excerpt

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

Functions:

D generate list excerpts
pages KTHLARGEST Function,KTHLARGESTUNIQUE Function

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:

D trans
RawWrangle true '1st' step derive type:single value:KTHLARGEST(Score, 1) as: '1st' Formula type Single row formula Formula KTHLARGEST(Score, 1) New column name New formula

D trans
RawWrangle true '2nd' step derive type:single value:KTHLARGEST(Score, 2) as: '2nd' Formula type Single row formula Formula KTHLARGEST(Score, 2) New column name New formula

D trans
RawWrangle true '3rd' step derive type:single value:KTHLARGEST(Score, 3) as: '3rd' Formula type Single row formula Formula KTHLARGEST(Score, 3) New column name New formula

D trans
RawWrangle true '4th' step derive type:single value:KTHLARGEST(Score, 4) as: '4th' Formula type Single row formula Formula KTHLARGEST(Score, 4) New column name New formula

D trans
RawWrangle true '3rdUnique' step derive type:single value:KTHLARGESTUNIQUE(Score, 3) as: '3rdUnique' Formula type Single row formula Formula KTHLARGESTUNIQUE(Score, 3) New column name New formula

D trans
RawWrangle true '4thUnique' step derive type:single value:KTHLARGESTUNIQUE(Score, 4) as: '4thUnique' Formula type Single row formula Formula KTHLARGESTUNIQUE(Score, 4) New column name New formula

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.

D s also
label example_kthlargestdate_functions