Hi, I’m a bit stuck with this problem.
I’m doing a reconciliation of 2 spreadsheets.
One has Order IDs with a summed out order total, but the other spreadsheet has duplicate Order IDs which sum up to order total when added together. See image for more details
What I’m trying to do is sum Total 2 with the same order IDs, while keeping the same amount of duplicate rows. Would love to hear your ideas. Thank you. In Excel it’s just SUMIF, I’ve tried groupby, but It just aggregates the duplicates. I want to sum the last column, while keep the same row structure.
Hi @miguelpg, welcome to the KNIME community. I think I understand what you’re asking but could you post a table or picture of what you’re desired output would be using the sample you’ve already shown. That way people will be better able to assist. Thanks
The upper table on the image above is the data I’m working with. I want to turn into the data like the table in the bottom half in the image above. As you can see the ones which have the same IDs in the first column, add up the values in the 6th column. So example for the first row, 35.002 adds up to 14.498, and the same thing happens to the second row, and the same with rows 3 and 4.
Hi @miguelpg , thanks for the additional information. That isn’t quite what I was thinking you meant, but I now understand what you are trying to do. I think what you need to do is, having used Group By to find your sum(total2) by ID2, you can then use cell replacer to bring the aggregation back into the original table. The attached workflow shows an example of what I mean. I hope that helps. The important part of this flow is the use of GroupBy and Cell Replacer. The nodes to the left of the flow are just me setting up the sample data into the form you have shown. (Incidentally, I assume that the “49.9” you had in the top table against ID12 was a typo, and you meant 59.9 as per the original screenshot, and the lower table)
