Sum between flagged rows

Hi Forum,

I try to achieve the following:

I have a table of chronological order like this

Col 1 - Col 2
E - 12
U - 4
P - 6
P - 7
E - 8
P - 7
P - 9
P - 9
U - 6
E - 8
P - 10
U - 12

I need the cumulative sum of the values of column 2 from the first row after an E occurs and until a U comes. I then need to move this sum inside the row with the E so that I can filter out the rest. For example the first E should have the value 4, the second E should have the value 7 + 9 + 9 + 6 = 31, and the third E should have the value 10 + 12 = 22.

How do I do this? I tried with lag columns but the inconsistency makes it really hard for me.

Thank you in advance
best

The biggest challenge actually is the rows between U and E. Without those, I would not have used scripting nodes. I generally stay away from those in forum answers, but I’m not sure how I would’ve done that.

  1. Java Snippet to determine start and end of a group. This is done using a row-persistent switch, which also allows intermediate rows. (E switches on, U switches off)
    1.a. the output is simultaneously used as group identifier.
  2. filter out rows that aren’t sandwiched between E and U
  3. filter out E rows
    3a. those 2 nodes could be done with a single Rule-based Row Filter, it’s an artifact from developing the solution. I’ll keep it because it’s easier on the brain
  4. GroupBy: Sum remaining values in each group
  5. Join back with original table
  6. the Joiner gives the sum in all rows of a group, so we remove them with a Rule Engine


sum between flagged rows.knwf (39.8 KB)

5 Likes

Thyme,

thank you very much. This solved my problem!

1 Like

Curious to see it without a snippet node. My first idea did not work but puzzles are fun

@Daniel_Weikert it’s funny that you mentioned it, after reading your post I had an idea how it could be done. 4 nodes were needed to replace the Java Snippet, although I didn’t try too hard to Node Golf this, it was more of a direct transformation of the functionality.


sum between flagged rows.knwf (105.5 KB)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.