I’m quite new to KNIME and have a question about how to loop through a column. I have data which I read from a Excel-file. Each of my rows represents amount. In column 6 I have amounts values which sometimes are duplicated (sometimes 2 rows have the same amount). I want to loop through this column (Col. 6) rows and if I found that there is a duplication, then I should sum column 1 rows amounts, else, 0. The result for each row should be added in a new column.
Where can I learn how to perform this kind of actions?
any assistance will be appreciated.
would you be able to upload a small sample spreadsheet (it can contain dummy data) along with an example of the required resultant spreadsheet as this will make it easier for us to visualise and assist you.
thanks for the feedback!
PFA an Excel sample.
if the amount column is duplicated and amount is not 0 than sum the code column in column dup, else dup = 0. Sample.xlsx (20.8 KB)
Hi @Sharon_c, thanks for the sample data. It’s quite an unusual request in that you effectively need to group by the amount and sum the code, which is the opposite way round to normal.
Once you’ve got your aggregate values for your groups, you can then join this back on amount to produce the dup column.
An additional Rule Engine can then set the dup column to zero where it equals the code (assuming no code is zero), or if (your additional rule) the amount is zero.
That ought to then mean that where there was no duplication, the dup value is zero.
I’ve attached a workflow demonstrating this.
I’ve also included in that flow a second variant that also returns a count, which would be necessary if, for example, a code could be zero or possibly negative and thus duplicates might not be detected using the first method.
Sum duplicates.knwf (24.8 KB)
yes, I know the logic is a bit awkward lol
thanks a lot!!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.