...

Calculate Cumulative Value with Possible Early Termination

 

Example

The data of Excel file kucun.xlsx are shown in the figure below, in which the four columns are the product code(Code), product location number(Place), inventory warehouse number(Stock place), and inventory quantity(Quantity). There is only inventory data of one product in the file.

..

Now it is known that the quantity of this product sold today is 50. Please calculate the new inventory number. According to the order of the table, the inventory quantity is cleared one by one, until 50 is cleared cumulatively, and finally only the records with the inventory quantity greater than 0 are kept.

Write SPL script:

A

1

=T("E:/kucun/kucun.xlsx")

2

=A1.iterate((a=min(Quantity,~~),Quantity-=a,~~-a),50,~~==0))

3

=A1.select(Quantity>0)

4

=T("E:/kucun/kucun1.xlsx",A3)

A1 Read the data of kucun.xlsx

A2 Use the iterate function to perform iterative loops on the data. In the loop, ~~ represents the result of the last iteration, and its initial value is set as 50. Take the Quantity of the current record and the minimum value of ~~, and assign them to the variable a. Subtract “a” from the number of the current record, and use “~~-a” as the result of this iteration; when the result of the iteration ~~ is 0, end the iteration. 

A3 Select the records with Quantity>0 in A1 after the iterative processing

A4 Store A3 to kucun1.xlsx

 


 
 •  317 View  •