4.24 Aggregate files - append and aggregate
There is a statistical table for daily purchase and delivery of goods:
There is also a summary table for daily purchase, delivery and inventory of goods:
Now we want to append the daily purchase and delivery data to the summary table to calculate the latest inventory: inventory of the previous day + purchase - delivery. The aggregation result is:
Script:
| A | |
|---|---|
| 1 | =T(“20200803.xlsx”).derive(Inventory) |
| 2 | =T(“total.xlsx”) |
| 3 | =A1.run(Inventory=A2.select@z1(Goods==A1.Goods).Inventory+Purchase-Delivery) |
| 4 | =file(“total.xlsx”).xlsexport@a(A3) |
A1: Read the current day data to be appended and aggregated and add a new “Inventory column.
A2: Read the data of summary table
A3: Loop through every row in A1 so that the value of Inventory is the Inventory of the last good in summary table plus the current Purchase and minus the current Delivery. @z1 option means selecting the first row that satisfies the condition from back to front
A4: Append and save the result of A3 to the file total.xlsx, and @a option means appending the data
esProc Desktop and Excel Processing
4.23 Aggregate files - aggregate by cell positions - unfixed number of files
4.25 Aggregate files - cumulate and aggregate
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/
