4.24 Aggregate files - append and aggregate

 

There is a statistical table for daily purchase and delivery of goods:

imagepng

There is also a summary table for daily purchase, delivery and inventory of goods:

imagepng

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:

imagepng

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