RaqForum 19 No.
1 Reply  •  460 View  • 
     ...

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


 
 •  460 View  •