...

Choose One Data in Each Category

 

The key function for this operation is group, which first groups the data and then selects according to requirements.

Example

Part of the Excel file of employee table "emp.xlsx" is shown below, in which the EID is in ascending order:

..

The task is to find the employee with the largest employee number in each department. The results are as follows:

..

Write SPL script:

A

1

=file("emp.xlsx").xlsimport@t()

2

=A1.group(DEPT).(~.m(-1))

3

=file("result.xlsx").xlsexport@t(A2)

 

A1 Read employee data from Excel

 

A2 Group the data by DEPT, and take the last record in each group [To find the employee with the smallest employee number in each department, the expression can be changed into: =A1.group@1(DEPT)]

 

A3 Export results to result.xlsx

 


 
 •  246 View  •