RaqForum 19 No.
1 Reply  •  358 View  • 
     ...

Find and Filter Data by Adjacent Values

 

Example

Part of the data in book1.xlsx is shown in the figure below:

..

The task is to extract data based on the Value2 column. Whenever the data changes from a negative value to 0, extract the row where “0” is located.

 

Write SPL script:

A

1

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

2

=A1.select(Value2==0   && Value2[-1]<0)

 

A1: Read the data in book1.xlsx

 

A2: Select the record whose Value2 is 0 and the Value2 in the previous row is less than 0. Value2[-1] represents the Value2 in the previous row.

 

The same rule applies to other values: Value2[-2] represents the Value2 two rows up, and Value2[2] represents the Value2 two rows down.


 
 •  358 View  •