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

Find Adjacent Rows in the Same Category

 

Example

In the following Excel, rows with the same Block belong to the same group.

Block

Bench

4

1

5

1

6

1

4

8

5

8

6

8

4

9

5

9

6

9

4

5

5

5

6

5

 

The task aims to calculate two new columns. BenchAbove is the Bench of the previous row in the same group; if this row is the first row, BenchAbove will be empty. BenchBelow is the Bench of the next row in the same group; if this row is the last row, BenchBelow will be empty. The results are as follows:

Block

Bench

BenchAbove

BenchBelow

4

1

8

5

1

8

6

1

8

4

8

1

9

5

8

1

9

6

8

1

9

4

9

8

5

5

9

8

5

6

9

8

5

4

5

9

5

5

9

6

5

9

 

The difficulty of this type of calculation is to get both the previous and the next rows in the same group without changing the order of rows.

Write SPL script:

A

1

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

2

=A1.derive(~[:-1].select@1z(Block==A2.Block).Bench:BenchAbove,

~[1:].select@1(Block==A2.Block).Bench:BenchBelow)

3

=file("result.xlsx").xlsexport@(A2)

A1 Read the data of Excel

 

A2 Get adjacent rows. ~[:-1] is used to get all rows before the current row, ~[1:] is to get all rows after the current row, select is used for searching, @z option means to search from back to front, @1 option means to end the calculation when one record is found.

 

A3 Export results to result.xlsx


 
 •  380 View  •