| 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:
| 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:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '1st' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:KTHLARGEST(Score, 1) as: '1st' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | KTHLARGEST(Score, 1) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '2nd' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:KTHLARGEST(Score, 2) as: '2nd' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | KTHLARGEST(Score, 2) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '3rd' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:KTHLARGEST(Score, 3) as: '3rd' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | KTHLARGEST(Score, 3) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '4th' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:KTHLARGEST(Score, 4) as: '4th' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | KTHLARGEST(Score, 4) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '3rdUnique' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:KTHLARGESTUNIQUE(Score, 3) as: '3rdUnique' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | KTHLARGESTUNIQUE(Score, 3) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '4thUnique' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:KTHLARGESTUNIQUE(Score, 4) as: '4thUnique' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | KTHLARGESTUNIQUE(Score, 4) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
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.
| D s also |
|---|
| label | example_kthlargestdate_functions |
|---|
|
