...

Find Intervals with Continuous Data

 

Example

There is an Excel file book1.xlsx, and the data is as follows:

..

Now we need to group the continuous records with setup in column B together, find the record with the smallest value in column A in the group, and write it on the new column C in the first row of the group. The result is as follows:

..

Write SPL script:

A

1

=file("book1.xlsx").xlsimport@t()

2

=A1.group@o(B).select(~.B).run(~(1).C=~.(A).min())

3

=file("result.xlsx").xlsexport@t(A1)

A1 Read the data in Excel file

 

A2 A1.group@o(B) is used to group column B by the same value in order, select(~.B) is used to find the data where B is not empty, select the smallest value of column A in each group, and assign it to the column C of the first record in the group

 

A3 Export the result to result.xlsx

 

 


 
 •  296 View  •