There is customer order information in the Excel file order.xlsx. Part of the data is as follows:

Order detail information in another file item.xlsx, part of the data is shown in the figure below:

Example1

Find out customer order information with a total order amount greater than 1000.

Write SPL script:

	A
1	=T("e:/work/order.xlsx")
2	=T("e:/work/item.xlsx")
3	=A2.groups(Orderkey;sum(Price*Quantity):amount).select(amount>1000)
4	=A1.join@i(Orderkey,A3:Orderkey)
5	=T("e:/work/result.xlsx",A4)

A1 Read order.xlsx

 

A2 Read item.xlsx

 

A3 A2 Group by Orderkey, calculate the total amount of orders in each group, and then select the group with amount>1000

 

A4 Join A1 and A3 according to the Orderkey, and the option @i means to discard the unmatched records in A1

 

A5 Save A4 to the file result.xlsx

 

After running, the result.xlsx file is as follows:

 

Example2

Find out the customer order details in the north region.

Write SPL script:

	A
1	=T("e:/work/order.xlsx")
2	=T("e:/work/item.xlsx")
3	=A1.select(Area=="north")
4	=A2.join@i(Orderkey,A3:Orderkey)
5	=T("e:/work/result.xlsx",A4)

A1 Read order.xlsx

 

A2 Read item.xlsx

 

A3 Select the order information for the north region from A1

 

A4 A2 and A3 are joined according to the Orderkey, and the option @i means to discard the unmatched records in A2

 

A5 Save A4 to the file result.xlsx

 

After running, the result.xlsx file is as follows: