EXAMPLE - KTHLARGEST Function
This example explores how you can use aggregation functions to calculate rank of values in a column.
Functions:
Item | Description |
---|---|
KTHLARGEST Function | Extracts the ranked value from the values in a column, where |
KTHLARGESTUNIQUE Function | Extracts the ranked unique value from the values in a column, where |
Source:
You have a set of student test scores:
Student | Score |
---|---|
Anna | 84 |
Ben | 71 |
Caleb | 76 |
Danielle | 87 |
Evan | 85 |
Faith | 92 |
Gabe | 87 |
Hannah | 99 |
Ian | 73 |
Jane | 68 |
Transformation:
You can use the following transformations to extract the 1st through 4th-ranked scores on the test:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | KTHLARGEST(Score, 1) |
Parameter: New column name | '1st' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | KTHLARGEST(Score, 2) |
Parameter: New column name | '2nd' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | KTHLARGEST(Score, 3) |
Parameter: New column name | '3rd' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | KTHLARGEST(Score, 4) |
Parameter: New column name | '4th' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | KTHLARGESTUNIQUE(Score, 3) |
Parameter: New column name | '3rdUnique' |
Transformation Name | |
---|---|
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:
Student | Score | 1st | 2nd | 3rd | 4th | 3rdUnique | 4thUnique |
---|---|---|---|---|---|---|---|
Anna | 84 | 99 | 92 | 87 | 87 | 87 | 85 |
Ben | 71 | 99 | 92 | 87 | 87 | 87 | 85 |
Caleb | 76 | 99 | 92 | 87 | 87 | 87 | 85 |
Danielle | 87 | 99 | 92 | 87 | 87 | 87 | 85 |
Evan | 85 | 99 | 92 | 87 | 87 | 87 | 85 |
Faith | 92 | 99 | 92 | 87 | 87 | 87 | 85 |
Gabe | 87 | 99 | 92 | 87 | 87 | 87 | 85 |
Hannah | 99 | 99 | 92 | 87 | 87 | 87 | 85 |
Ian | 73 | 99 | 92 | 87 | 87 | 87 | 85 |
Jane | 68 | 99 | 92 | 87 | 87 | 87 | 85 |
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.