Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r093
Excerpt

This example explores how you can use aggregation functions to calculate rank of values in a column.

Functions:

D generate list excerpts
pagesKTHLARGEST Function,KTHLARGESTUNIQUE Function

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:

D trans
RawWrangletrue
p03Value'1st'
Typestep
WrangleTextderive type:single value:KTHLARGEST(Score, 1) as: '1st'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKTHLARGEST(Score, 1)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'2nd'
Typestep
WrangleTextderive type:single value:KTHLARGEST(Score, 2) as: '2nd'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKTHLARGEST(Score, 2)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'3rd'
Typestep
WrangleTextderive type:single value:KTHLARGEST(Score, 3) as: '3rd'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKTHLARGEST(Score, 3)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'4th'
Typestep
WrangleTextderive type:single value:KTHLARGEST(Score, 4) as: '4th'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKTHLARGEST(Score, 4)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'3rdUnique'
Typestep
WrangleTextderive type:single value:KTHLARGESTUNIQUE(Score, 3) as: '3rdUnique'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKTHLARGESTUNIQUE(Score, 3)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'4thUnique'
Typestep
WrangleTextderive type:single value:KTHLARGESTUNIQUE(Score, 4) as: '4thUnique'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKTHLARGESTUNIQUE(Score, 4)
p03NameNew column name
SearchTermNew formula

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.

D s also
labelexample_kthlargestdate_functions