...

Select the Top N and Last N

 

Example

Part of the data in the sales table book1.xlsx is shown in the figure below:

..

Sort the total sales amount from the largest to smallest, and find the top 10 salesmen and their total sales amount.

Write SPL script:

A

1

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

2

=A1.groups(Name;sum(Sales):Sales)

3

=A2.top(-10,Sales)

4

=A2.top(-10;Sales)

 

A1 Read the data in book1.xlsx

 

A2 Group the data by Name, count the sum of Sales in each group, and name them as Sales

 

A3 Find the top 10 values of Sales, and the parameters are separated by commas

 

A4 Find the top 10 records with the largest Sales, and the parameters are separated by semicolons

 

The parameter -10 means that the top 10 is taken after sorting in descending order, that is, the top 10 with the largest values.

 

If 10 is used as the parameter, it means that the top 10 is taken after sorting in ascending order, that is, the top 10 with the smallest values, which is equivalent to the last 10 records.

 

 


 
 •  235 View  •