How to count values of the same group, with intermediate counter reset?

Hi to everyone!

I hope that it is the correct category in which to ask the following question.

I have a table with the column1 values, so only 0 and 1, I’d like to append a new column in which:

  • if column1 == 1 --> the new column have to display 0
  • if column1 == 0 --> the new column have to display the distance from the next 0 in column1

In the following I have attached the wanted resulting table.

Using Group Loop node, the 0s and 1s will be sorted, but I need to mantain the original column1 order.
Can someone helps me?

Tranks,
Andrea

This forward looking type of operation is typically best performed using either time series nodes or the Python/R scripting integrations which allow you to load the whole table at once. If you are allergic to scripting or just want to try in native knime first, I would start with the Window Loop Start node.

Hi Aaron_Hart, thanks for the reply!

It is became a challenge!
In my opinion it is impossible that there is not a way to refers to the previous/next cell, already modified, in a table.
I’m new of KNIME (previously I used to use matlab for everithing), and I think that I have not understood its real spirit, yet.

Now I’m using the Window Loop Node (as you’ve suggested) to select the sigle row, with a Case SWITCH Data Node controlled by the column1 variable.
If the control number is 1, I put 0 in the appended column; in the case of 0 the counter append a new column, that ise always the min value of the counter… it is frustrating!

Hello @aesposito -

Here’s one way you could do it, without a loop. This workflow uses the Moving Aggregation node to look forward in the data, and then does some manipulation on the results to determine what the final values should be. I’ve left the interim steps as individual columns in the table so the logic is a bit clearer.

The downside is that this workflow is hardcoded to look ahead 5 observations. It’s easy enough to change that in the Moving Aggregation node, but still, it’s something to be aware of. As usual with most workflows, there is probably a more elegant way to do this, but hopefully this is enough to get you started.

2018-09-14%2013_27_38-Classified%20values%20-%203_35%20-%20Rule%20Engine%20(Correct%20for%20_1_)

LookForwardExample.knwf (14.1 KB)

2 Likes

Hi Scott, thanks for your reply.
Your workflow was very useful and helpful for my purpose. You also make me discover the ‘aggregation power’ :smiley:

I’d like to purpose a different solution that, in my opinion, is easier and more intuitive than yours. Maybe I can help someone. I’m newbie of KNIME, so correct me if I’m wrong! :+1::+1:

image

In my workflow was also useful the normal count, so I attach also this solution in the following.

Andrea

LookForward_CompareScott LookBackward.knar (32.9 KB)

2 Likes

Looks good to me! I’m glad you were able to work out a solution. It’s always good to see multiple successful approaches to the same problem :slight_smile: