...

Calculate Data by Adjacent Rows and Intervals

 

Examples

Example1: Calculate with adjacent rows

Part of the data in the Excel file is shown in the figure below:

..

Process the data: If column A is empty, copy the value of column C of this row to column D of the previous row, continue such operation in a loop, and then delete the blank rows. The result is as follows:

..

Write SPL script:

A

1

=file("e:/work/book1.xlsx").xlsimport@w()

2

=A1.run(if(~(1)==null,~[-1]=~[-1]|~(3)))

3

=A1.select(~(1))

4

=file("e:/work/book2.xlsx").xlsexport@w(A3)

A1 Read the data of book1.xlsx. The @w option means to read the data as the sequence of sequences 

A2 Loop through each row in A1. If the first member is empty, merge the third member to the end of the previous row. ~[-1] means the previous member (row) of the current member (row) 

A3 Select the first member in A1 (value of column A) that is not empty (row)

A4 Store the results in A3 to book2.xlsx, the @w option indicates that the exported A3 is the sequence of sequences

 

Example2: Calculate with adjacent intervals

The Excel file sales.xlsx stores someone’s sales amount data, as shown in the figure below:

..

The task is to calculate the date when the sales amount is over 50,000.

Write SPL script:

A

1

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

2

=A1.select@1(Sales[:0].sum()>50000).Date

A1 Read the data of sales.xlsx as a table sequence

A2 Select a row of records from A1. If the sum of Sales from the first row to the current row is greater than 50000, then select this row and return the Date value of the row. Sales[:0] means the sequence composed of the Sales fields from the first row to the current row, and the @1 option means to select the first record that meets the condition


 
 •  248 View  •