...
4.25 Aggregate files - cumulate and aggregate
There are statistical tables for the daily sales of some goods in current month, one table per day. Now we want to add the cumulative value to the column “monthly cumulative sales” of these files.
Before merging:
20220101.xlsx
20220102.xlsx
20220103.xlsx
Files of other dates are omitted.
After merging:
20220101.xlsx
20220102.xlsx
20220103.xlsx
Files of other dates are omitted.
Script:
| A | B | |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
| 2 | =periods(A1,B1).(string(~,“yyyyMMdd”)+“.xlsx”) | |
| 3 | =A2.(T(~)) | |
| 4 | >A3(1).run(MonthlyCumulativeSales=DailySales) | |
| 5 | for A3.to(2,) | =A5.run(MonthlyCumulativeSales=DailySales+A3(#A5).select@1(Name==A5.Name). MonthlyCumulativeSales) |
| 6 | =A3.run(T(A2(#),~)) | |
