...

Filter Data Categories by Condition of Aggregate Value

 

The key functions for such operation are groups and select, which are used to group and summarize the data first, then filter them to get the expected result.

Example

There is an Excel file of employee table “emp.xlsx”, and part of the data is as follows:

..

Now we need to find out which departments (DEPT) have the sum of salary (SALARY) greater than 500,000. The results are as follows:

Marketing

Production

Sales

Write the SPL script:

A

1

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

2

=A1.groups(DEPT;sum(SALARY):total)

3

=A2.select(total>500000).(DEPT)

 

A1 Read employee data from Excel

A2 Group the data by DEPT, and sum SALARY

A3 Filter out the departments whose sum of SALARY is greater than 500,000

 


 
 •  293 View  •