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

Associating Tables through Multi-Columns

 

Example

The data of Excel file book1.xlsx is shown in the figure below.

..

Another file, book2.xlsx, has the following data:

..

The task is to use the values of A, B, and C in each row of book1.xlsx to find the corresponding values of M, N, and O in book2.xlsx, and then return the House values of the current rows to fill in the E column of book1.xlsx. The result is shown in the figure below:

..

WriteSPL script:

A

1

=T("E:/work/book1.xlsx").derive(House)

2

=T("E:/work/book2.xlsx").keys(M,N,O)

3

=A1.run(House=A2.find([A,B,C]).House)

4

=T("E:/work/book1.xlsx",A1)

A1 Read the data in book1.xlsx and add a new column House

A2 Read the data in book2.xlsx and specify M, N, O as key fields

A3 Loop through each row of A1, find the corresponding rows using the key field values in A2 which are corresponded to the values of A, B, and C, and assign them to the House column of A1

A4 Store A1 to book1.xlsx


 
 •  470 View  •