...

4.14 Rank row-wise data by multiple fields

 
spl-cookbook(224)

Rank records of the following score table by Math field, English field and PE field in order:

Class Name Math English PE
1 Tom 85 97 74
2 John 92 80 95
2 Joan 86 67 85
1 Rocky 95 95 75
2 Ham 92 75 53
1 Kate 83 99 50
1 Rose 95 62 81
2 Nomy 78 91 70
1 Neil 91 83 97
2 Jack 84 75 73
1 Joe 98 71 60

SPL script:

A
1 =T(“score.xlsx”)
2 =A1.([Math,English,PE])
3 =A1.derive(A2.rank@z([Math,English,PE]):Rank)

A2 Get a sequence of sequences of values in Math field, English field and PE field.
A3 Add a Rank field to A1 and calculate the ranks of math score, English score and PE score of the current row using A2’s expression.

Execution result:

Class Name Math English PE Rank
1 Tom 85 97 74 8
2 John 92 80 95 4
2 Joan 86 67 85 7
1 Rocky 95 95 75 2
2 Ham 92 75 53 5
1 Kate 83 99 50 10
1 Rose 95 62 81 3
2 Nomy 78 91 70 11
1 Neil 91 83 97 6
2 Jack 84 75 73 9
1 Joe 98 71 60 1

 

spl-cookbook(224)
 
 •  192 View  •