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

Associating a Single Column with Multiple Columns of Another Table

 

Example

The Excel file Book1.xlsx is a table of standard freight rates. Part of the data is as follows:

city

first1KG

add1KG

Alabama

12

4

Alaska

12

4

Arizona

12

4

Arkansas

12

4

Boston

12

4

California

12

4

Colorado

12

4

Connecticut

12

4

…

…

…

The Excel file Book2.xlsx is a freight fee table. The data is as follows:

oid

city

weightKG

fee

100001

Alaska

15

100002

Arkansas

13

100003

Boston

11

100004

Montana

3

100005

Juneau

2.5

100006

Ohio

8

100007

Denver

3.6

100008

Montana

22

100009

Nevada

19

According to the standard freight rate table, calculate the actual freight fee. The results are as follows:

oid

city

weightKG

fee

100001

Alaska

15

68

100002

Arkansas

13

60

100003

Boston

11

52

100004

Montana

3

25

100005

Juneau

2.5

25

100006

Ohio

8

40

100007

Denver

3.6

30

100008

Montana

22

120

100009

Nevada

19

105

Write SPL script:

A

1

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

2

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

3

=A2.join(city,A1:city,first1KG,add1KG)

4

=A3.new(oid,city,weightKG,first1KG+add1KG*(ceil(weightKG)-1):fee)

5

=file("result.xlsx").xlsexport@t(A4)

A1 Read the data of Book1.xlsx

A2 Read the data of Book2.xlsx

A3Associate the two tables according to the city

A4 Calculate the actual freight fee

A5Export results to result.xlsx


 
 •  483 View  •