Remove paired data
Example
There is an Excel file Book1.xlsx, and part of the data is as follows:
id |
name |
item |
amount |
1001 |
alice |
eye |
60 |
1001 |
alice |
eye |
-60 |
1002 |
tom |
nose |
30 |
1003 |
jerry |
stomach |
70 |
1003 |
jerry |
stomach |
-70 |
1003 |
jerry |
hand |
50 |
1004 |
bob |
arm |
100 |
1005 |
jack |
leg |
25 |
1005 |
jack |
leg |
-25 |
The task is to delete the rows where the id and item are duplicate and the amount can be offset by the positive and negative values. The result is as follows:
id |
name |
item |
amount |
1002 |
tom |
nose |
30 |
1003 |
jerry |
hand |
50 |
1004 |
bob |
arm |
100 |
Write SPL script:
A |
|
1 |
=file("Book1.xlsx").xlsimport@t() |
2 |
=A1.group(id,item).select(~.sum(amount)!=0).conj() |
3 |
=file("result.xls").xlsexport@t(A2) |
A1 Read the excel file
A2 Group the data by id and item, filter out the data whose sum of amount is not 0 in the group, and merge the selected data
A3 Export results to result.xlsx
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
