RaqForum 22 No.
1 Reply  •  386 View  • 
     ...

Aggregating Uncertain Number of Columns

 

Example

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

..

The task is to calculate the aggregate values (of C~F columns) in the blank cells of each group, and the results are as follows:

..

Write SPL script:

A

B

1

=file("sum.xlsx").xlsimport@w()

2

=A1.group@i(~[-1](1)==null)

3

for A2

=A3.select(~(1))

4

=B3|[A3.m(-1)|transpose(B3.(~.to(3,))).(~.sum())]

5

>B1|=B4

6

=file("sum_result.xlsx").xlsexport@w(B1)

A1 Read the excel file and return the sequence of sequences

A2 Create a new group when the previous cell of the first column is empty

A3 Loop through each group

B3 Filter out aggregate rows

B4 Append the aggregate result to B3

B5 Append the result of each group to B1

A6 Export the result to sum_result.xlsx


 
 •  386 View  •