...

Filter Data by the Aggregate Value of Category

 

Example

The Excel file "emp.xlsx" is an employee table, and part of the data is as follows:

..

Now we need to find out the data of employees whose salary (SALARY) is greater than the average salary of 5000 in the department. The results are as follows:

..

Write SPL script:

A

1

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

2

=A1.group(DEPT)

3

=A2.run(avg=~.avg(SALARY),~.run(diff=SALARY-avg))

4

=A1.select(diff>5000).new(EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY)

5

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

 

A1 Read employee data from Excel

 

A2 Group the data by DEPT

 

A3 In each group, calculate the difference between the current employee's salary and the average salary in the group

 

A4 Find data where the salary difference is greater than 5000

 

A5 Export results to result.xlsx


 
 •  221 View  •