...

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


 
 •  277 View  •