Page tree

Release 9.2


Contents:

   

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.