RaqForum 28 No.
1 Reply  •  369 View  • 
     ...

How to Group and Aggregate If Neighboring Rows Meet the Specified Condition

 
excel(115) order-based calculation(12) group(7) inter-row calculation(4)

Here’s Excel file book1.xlsx:

051000

55.74

055000

61.47

056000

44.53

062000

142.11

081000

142.11

088000

44.53

092000

52.89

095100

118.37

096500

354.8

096700

6.49

099100

44.53

102600

162.74

We want to group rows by the first column and then sum the second column for each group. The grouping rule is like this: Create a new group if the current value of column 1 is non-null and the previous value is null and the one before the previous value is different from the current value in terms of the first two digits. Below is the expected result:

051000

161.74

062000

142.11

081000

186.64

092000

577.08

102600

162.74

It would be effortless if we could handle this with esProc.
Download esProc installation package and free DSK edition license HERE.

1.     Write script groups.dfx in esProc:

A

1

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

2

=A1.group@i(if(#1 &&   #1[-1]==null && left(#1,2)!=left(#1[-2],2)))

3

=A2.new(#1:section,round(~.sum(#2),2):amount)

 A1   Import data of book1.xlsx.

A2   Group rows according to the specific condition (The current value of the first column is non-null, and the previous value is null, and the first two digits of the one before the previous value are different from those in the current value).

A3   Sum values of the second column in each group. section represents the first value of the first column in each group.

2.     Execute script to get the desired result in A3.


excel(115) order-based calculation(12) group(7) inter-row calculation(4)
 
 •  369 View  •