Ranking in Category

 

Example

The Excel file book1.xlsx is the students total score table, and part of the data is shown in the figure below:

..

 

The task is to calculate the students total score ranking in the Class and fill the results in Rank column, as shown in the figure below:

..

 

Write SPL script:


A

1

=T("E:/work/book1.xlsx")

2

=A1.group(Class).run(a=~.ranks@z(Score),~.run(Rank=a(#)))

3

=T("E:/work/book2.xlsx",A1)

 

A1 Read the data of book1.xlsx

 

A2 Group the data by class, loop through each group, calculate the ranking sequence of each students total score in the group, and assign it to variable a. Loop through each student in the group, and assign the rank of the corresponding row number # in a to the Rank field. The @z option means to rank from the largest score to the smallest one

 

A3 Store A1 to book2.xlsx