12.10 Associate with detail table
Here below is a data table that stores the customer order information:
The following data table stores the order details:
Example 1:
Find out the customer order information with a total order amount greater than 1000:
| A | |
|---|---|
| 1 | =E(‘Sheet1!A1:D8’) |
| 2 | =E(‘Sheet2!A1:E17’) |
| 3 | =A2.groups(OrderID;sum(Price*Quantity):Amount).select(Amount>1000) |
| 4 | =A1.join@i(OrderID,A3:OrderID) |
A3: Group A2 by OrderID; calculate the total order Amount of each group; select the group with Amount>1000
A4: Join A1 and A3 on OrderID. The option @i means discarding the unmatched row in A1
Example 2:
Find out the order details in the north area.
Script:
| A | |
|---|---|
| 1 | =E(‘Sheet1!A1:D8’) |
| 2 | =E(‘Sheet2!A1:E17’) |
| 3 | =A1.select(Area==“north”) |
| 4 | =A2.join@i(OrderID,A3:OrderID) |
A3: Find out the order information in the north area from A1
A4: Join A2 and A3 on OrderID. The option @i means discarding the unmatched rows in A2
esProc Desktop and Excel Processing
12.9 Associate multiple rows of data
12.11 Find changes through comparison
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
