...

Filter Data with Maximum and Minimum Values in a Category

 

The key functions for this operation are group, minp, and maxp, which group the data first, and then find by the maximum and minimum values.

Example

Part of the Excel file of employee table "emp.xlsx" is as follows:

..

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

..

Write SPL script:

A

1

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

2

=A1.group(DEPT)

3

=A2.(~.maxp(HIREDATE))

4

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

 

A1 Read employee data from Excel

 

A2 Group the data by DEPT

 

A3 Find the record with the largest HIREDATE value in each group

 

A4 Export the result to result.xlsx

 

 


 
 •  244 View  •