Sequential Occurrence of consecutive Values in a Column

[bzm9ba]

Hello,
I got the following issue.

I want to count occuring values.
I the value in the next row is the same, the counter should not add +1.

Does anyone know how to realize that in KNIME.
I could write python, but want to avoid that.

Thanks.

[gonhaddock]

Hello @bzm9ba
2 Options

1. Replicate your column by using String Manipulation [ join($Counter$) ] >> $new column$ (or with a ‘Rule Engine’…). Then you can test with ‘Group By’ node; by including both $Value$ and $Counter$ columns in:
   Group Settings (Groups Tab) >> Group column(s)
   and ‘Aggregation Settings’ $new column$ ‘Count’
   

2. ‘Group By’ node; by including only $Value$ column in:
   Group Settings (Groups Tab) >> Group column(s)
   and ‘Aggregation Settings’ $Counter$ ‘Unique Concatenate with count’
   

The resulting matrix will give you different display…

BR

[bzm9ba]

Hello Thanks for the suggstion,
but that is not the issue.
I want to generate the counter column, based on the Values.

[gonhaddock]

@bzm9ba
Then you can try a ‘Rank’ node:

BR

[bzm9ba]

OK, but initially I do not have the counter column.

I want to create the “Counter” column based on the “Value” column

[gonhaddock]

I got it,
Starting with $Value$ and generate $Counter$
Few more minutes to put it in nice…

[bzm9ba]

Yes sorry,
I didn t find a way to modify my post

[gonhaddock]

Hi @bzm9ba
I guess this answers the challenge:

20220920_sequential_and_consecutive.knwf (54.2 KB)

BR

PS.- Forgot to activate ‘Resolve missing values for incomplete windows’ 's check box in ‘Moving Aggregation’ node, as control for the last row.

[gonhaddock]

Count Sequential Occurrence of consecutive Values in a Column

The previous version had a bug… you can appreciate in Row3 x $rank$, given by an empty $prediction$ value in the in the latest row, then replacing the 3rd row. This new workflow deals with last row issue. [image] [image] 20220920_3_sequential_and_consecutive.knwf (61.8 KB) BR

[iCFO]

Seems like you could also use a loop approach as seen here, but use the iteration number +1 as the count instead of moving average.

Count consecutive the number of occurences string

Hi Mauu, you can do this via looping and the moving aggregation node.   First construct a group loop using your Element to count as group columns. Than use the Moving Aggregation node with the cumulative computation option and count as aggregation method. Finally close the loop with a loop end. I hope this helps. Best, Iris 

You would have to group by consecutive as well though.

[iCFO]

Not as clean as I envisioned because of the need to apply the group loop to sequential occurrences only, but it was still a fun loop approach to test out.

consecutive_counting_rank.knwf (59.8 KB)