Use interval range to look up association table

Example

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

id start end val
1001 1 54
1001 55 100
1001 101 149
1001 150 200
1002 1 10
1002 11 24
1003 1 2
1003 3 6
1003 7 8
1003 9 21
1003 22 30

 

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

id num val
1001 5 2
1001 77 5
1002 9 1
1003 11 4

 

To calculate the val column in Book1.xlsx, the requirements are:

 

1. The id column of Book2 is equal to the id column of Book1.

 

2. When condition 1 is met, judge whether the num of Book2 belongs to the range value of start to end of Book1

 

3. If the above two conditions are met at the same time, the val of Book1 is the val corresponding to Book2, and the result is as follows:

id start end val
1001 1 54 2
1001 55 100 5
1001 101 149
1001 150 200
1002 1 10 1
1002 11 24
1003 1 2
1003 3 6
1003 7 8
1003 9 21 4
1003 22 30

Write SPL script:


A
1 =file("Book1.xlsx").xlsimport@t()
2 =file("Book2.xlsx").xlsimport@t()
3 =A1.run(val=A2.select@1(id==A1.id && num>=A1.start   && num <=A1.end).val)
4 =file("result.xlsx").xlsexport@t(A1)

A1 Read excel file

 

A2 Read excel file

 

A3 Determine whether the data of each Book1 meets the requirements, and if it meets the requirements, the val of Book2 is given to Book1

 

A4 Export result to result.xlsx