RaqForum 22 No.
1 Reply  •  323 View  • 
     ...

Append and accumulate data to a summary table

 

Examples

1. Append

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

..

In addition, the daily purchase, delivery, and inventory summary table of products are as follows:

..

Now the task is to append 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:

A

1

=T("e:/work/20200803.xlsx").derive(Inventory)

2

=T("e:/work/total.xlsx")

3

=A1.run(Inventory=A2.select@z1(Goods==A1.Goods).Inventory+Purchase-Delivery)

4

=file("e:/work/total.xlsx").xlsexport@a(A3)

A1 Read out the data of the current day that needs to be appended to the summary table and add a new column of Inventory

A2 Read the data of summary table

A3 Loop through each row in A1 and set the value of Inventory as the last Inventory of the current product in the summary table plus the current Purchase and minus the current Delivery. @z1 option means to select the first record that meets the conditions from back to front

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

 

2. Accumulate

There is a daily sales summary table, in which one column is the sales summary of the current month. The following figure shows the table on August 1st, and the month sales column have been filled in:

..

The figure below shows the table on August 2nd with only day sales column. All other tables of this month are similar.

..

Now we need to fill in all the month sales columns in the daily sales tables.

Write SPL script:

A

B

1

=directory@p("e:/sales/*.xlsx").sort()

2

=A1.(T(~))

3

for A2.to(2,)

=A3.run('Month sales'='Day sales'+A2(#A3).select@1(Name==A3.Name). 'Month   sales')

4

=A2.run(T(A1(#),~))

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

A2 Read out all the data of summary tables in A1 in a loop as a table sequence

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

B3 Loop through each record in the current summary table so that the month sales is the day sales plus the month sales of the person on the previous day 

A4 Loop through all calculated summary tables in A2 and store them to the file names of the corresponding sequence numbers in A1

 


 
 •  323 View  •