Append and accumulate to a summary table

Example

1. Append

There is a statistical table of daily merchandise purchase and delivery as shown below:

In addition, the daily purchase, sales, and inventory summary of commodities are as follows:

Now it is necessary to add one day’s purchase and delivery data to the summary table and calculate the new inventory: the previous day’s inventory + purchase-delivery

Write SPL script:

A1 Read out the data that needs to be added to the summary of the day and add a column of Inventory

A2 Read the summary table data

A3 Loop through each row in A1 and set the value of Inventory as the last item in the summary table. Its value is the Inventory of the current product plus the current Purchase minus the current Delivery. Option @z1 means the last record that meets the conditions

A4 Append the results in A3 to the file total.xlsx, and the option @a means append data

2. Accumulate

There is a daily sales summary table. One column in the table is the current month’s sales summary. The following figure shows the table on August 1st. The monthly sales have been filled in:

The figure below shows the table on August 2 with only sales for that day. Other forms of this month are similar.

Now you need to fill in all the monthly sales in the daily table.

Write SPL script:

A1 List and sort the files of the daily sales summary table for this month. The option @p means to list the full path

A2 Read out all the summary table data in A1 cyclically as a table sequence

A3 Start the cycle from the summary table on the second day

B3 Loop through each record in the current summary table so that the monthly sales are the daily sales plus the monthly sales of the person on the previous day

A4 Loop throught all calculated summary tables in A2 and save them to the file name of the corresponding serial number in A1