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