EXAMPLE - KTHLARGEST Function

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

• See KTHLARGEST Function.
• See KTHLARGESTUNIQUE Function.

Source:

You have a set of student test scores:

Transform:

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

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

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

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

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

derive type:single value:KTHLARGESTUNIQUE(Score, 3) as: '3rdUnique'

derive type:single value:KTHLARGESTUNIQUE(Score, 4) as: '4thUnique'

Results:

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

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.