Contents:
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 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:
| 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 | 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:
| 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
87is both the third and fourth scores.- For the
KTHLARGESTfunction, it is the output for the third and fourth ranking. - For the
KTHLARGESTUNIQUEfunction, it is the output for the third ranking only.
- For the
This page has no comments.