How to create a row that is the sum of results in other rows and is also based on the criteria of other columns

Hi guys,

I have the following table:

There are two rows per Item ID and what I’m trying to do is add a visualised error check row for each item, below the Sum rows and the SumComponents rows to check if there is any difference between these for any items.

This is the desired table:

Does anyone know what the best node/nodes to use here are? I’ve been trying to play around with the pivot, unpivot, group by, rule engine and math formula nodes, etc… but I’m struggling to come up with a solution.

Any help would be much appreciated!

Hi @datascienceduck welcome to KNIME Forum

See this wf sub_totals_in_table.knwf (60.5 KB).
Schermafdruk van 2022-12-18 13-44-20
It uses a Group Loop for every Item ID. With the Tranposed data set it is possible to make the calculation.

gr. Hans


Hi @datascienceduck and welcome to the Knime Community.

I have a few questions:

  1. Is the Row ID just for reference for your explanation, or is it part of the data where we need to reset it after inserting the new rows?
  2. I don’t quite understand the values that you have in Row ID 6 in your DESIRED TABLE. Where did these values come from? (If I were to guess, I would say that you just pasted what you have on Row ID 3, but I’m not gonna spend time building something on a wrong assumption)
  3. Should the difference be an absolute value (always positive, for example the difference is 2 between 8 and 10, or between 10 and 8) or it could be a negative value (therefore, beyond the difference, you actually want the value of possibly Row Sum - Row SumComponents? Or vice versa?) - Actually, based on your explanation, you want “to check if there is any difference between these for any items”, so in theory, that should return either true or false, but that’s not what you have in your sample.

Hi @bruno29a , thanks for your reply and welcome!

  1. The row IDs are for my reference only and do not need to be reset after inserting new rows.
  2. Yes, sorry, that was a mistake on my part. The difference row should always be the value in the Sum row for an Item minus the value in the SumComponents row for the same Item ID.
  3. Yes it can be a positive or negative, and yes I’ve phrased my question badly - I need to know what the value of the difference is, so a TRUE or FALSE result won’t suffice.

Thanks so much for this Hans!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.