...

Associating Tables through a Single Column

 

Example

The Excel file Book1.xlsx is an order detail table, and the data is as follows:

id

date

amount

1

2019/9/8

3000

1

2019/10/8

1500

2

2019/11/20

1560

2

2019/11/28

2060

The Excel file Book2.xlsx is a user table, and the data is as follows:

id

name

sex

1

smith

male

2

alice

female

The task is to calculate the total consumption amount of male and female users. The results are as follows:

sex

total

female

3620

male

4500

WriteSPLscript:

A

1

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

2

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

3

=A1.join(id,A2:id,sex)

4

=A3.groups(sex;sum(amount):total)

5

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

A1 Read the excel file Book1.xlsx

A2 Read the excel file Book2.xlsx

A3 Associate two tables according to id

A4 Group and aggregate to get the result

A5 Export the result to result.xlsx


 
 •  202 View  •