Creating an IF/OR column which adds to the previous row.

[Clinton]

Hi, I am trying to create a workflow where it would read the value in a cell, and if the value of the cell is “real”, then add 1 to the column, else add 0, however I am not quite sure how to go about doing this. I’ve done this in Excel, however I was wanting to achieve the same result in KNIME so I do not need to go back and forth between KNIME and Excel.

I’ve tried to use the rule engine node, but I’m not quite sure how to syntax it properly.

I have attached an excel file with the column and what I would like to achieve. Any help would be appreciated. SingleTest.xlsx (35.0 KB) .

Thanks,
Clinton

[daniela_digles]

Hi @Clinton,
I’ve created a workflow that should (at least partially) solve this task.
add to previous row.knwf (37.4 KB)
There is room for improvement (see issue later), but maybe it gives you some ideas how to solve it. So far the only node to work with the value of the previous row I found is the Missing Value node. So how does it work? The Rule Engine node writes the rowID to the new column “real rows” (this way, you have a different value for every real row). Next the Missing Value node fills the missing values with the value from the previous row. A Group Loop start keeps the rows for each of the blocks (important to check the “input is already sorted” option here, otherwise it will resort alphabetically and make a mess). The Constant Value Column writes the currentIteration variable of the loop to the “Output” column. So this will start with 0 for the first block, and give each other block the previous number +1. The issue here is: if the first row contains a “real”, this will still start with 0.
I hope this is still useful, at least to get some ideas.
Best regards,
Daniela

[Daniel_Weikert]

You can do it with rule engine and moving aggregation
for rule engine
$Input$=“real” =>1

and for moving aggregation choose cumulative and sum

best regards

[Clinton]

Hi all,

Thanks for the fast responses. Both methods work perfectly. I’ll look into how to solve the issue if the first row contains real then it starts with 0 though.

Thanks again.
Clinton

[daniela_digles]

Thank you @Daniel_Weikert, this is really the better solution, as it also works if there is a ‘real’ case in the first row, and it is much easier. I was not aware of the Moving Aggregation node, this is really nice!
One addition: if you add TRUE => 0 to the Rule Engine, there will be no missing values in the result.

Best regards,
Daniela