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 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
