...

Associate multiple rows of data

 

Example

There is an Excel file Book1.xlsx, and part of the data is as follows:

aid

bid

cid

XD19010576

SCD181202515

XO18120141

XD19010577

SCD181202515

XO18120141

XD19010578

SCD181202515

XO18120141

XD19010579

SCD181202515

XO18120141

XD19010580

SCD181202515

XO18120141

XD19010581

SCD181202515

XO18120141

XD19020009

SCD181202515

XO18120141

XD19020010

SCD181202515

XO18120141

…

…

…

 

There is an Excel file Book2.xlsx, the data is as follows:

bid

cid

SCD181202515

XO18120141

SCD181202514

XO18120142

SCD181202612

XO18120253

SCD181202619

XO18120254

SCD181202614

XO18120255

 

The bid and cid of Book1 are correspondingly related to the bid and cid of Book2. Find the aid for each group. Part of the results are as follows:

SCD181202515

XO18120141

XD19010576

XD19010577

…

SCD181202514

XO18120142

XD19020014

XD19020016

…

SCD181202612

XO18120253

XD19030468

XD19030478

…

SCD181202619

XO18120254

XD19020153

XD19020156

…

SCD181202614

XO18120255

XD19030083

XD19030085

…

 

Write SPL script:

A

1

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

2

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

3

=A1.group(bid,cid;~.(aid):aid)

4

=A3.align(A2:[bid,cid],[bid,cid])

5

=A4.(bid|cid|aid)

6

=file("result.xlsx").xlsexport@w(A5)

 

A1 Read excel file

 

A2 Read excel file

 

A3 Group Book1 according to bid and cid, and each group has multiple aids

 

A4 The bid and cid of Book1 are associated with the corresponding relationship of Book2

 

A5 Combine the bid, cid, and aid columns

 

A6 Export the result to result.xlsx


 
 •  295 View  •