separating acording to a parameter to add up the total

[Bleck]

Hello i been trying to separate this tables, on the column “Histórico” i reach a point called total many times, i need to test all the lines above by adding them and checking if it adds up to the value (basically is a test to see if the calculation is correct) we have a column named “calcul” and that is done by hand outside Knime but we want to make it part of the test any way to do that
(so add up the values up to the total on the “Histórico” colunm?)

[bruno29a]

Hi @Bleck , here’s what you can do:

1. Use the Rank node to generate unique identifier for each Total row
2. Use the Missing Value node to fill all the above rows of each Total row with the same identifier of the Total row
3. Use the GroupBy node by grouping by the identifier and doing a sum on Valor as aggregator

That should give you the sum of the Valor for each Total row.

I can’t put anything together as I don’t have the data. If you shared the data, that would help.

[Bleck]

Condominio.xlsx (377.9 KB)
here is the document (had to check if there was no important info in it)
also i tried the Rank node but it does not leave empty cells for the missing value to fill
other than that will keep trying here (also thank you very much for your time)

[Daniel_Weikert]

if you use the moving aggregation and then the column expression you get the grouping @bruno29a was referring to and then you can follow his advice
br

[bruno29a]

Hi @Bleck , sorry for the delay, I’m quite busy during the day with work.

If you rank using the Ordinal Ranking Mode, it will give you the expected results:

But there are multiple other ways to reach the expected results, one way is by what @Daniel_Weikert suggested - and I know at least 3 other ways to do this.

And actually, I got inspired to use the Moving Aggregator as my solution, so I’m going in a whole different direction that my original suggestion

So basically the idea is to aggregate all the rows, with the TOTAL as negative value. So if you aggregate all the values above the TOTAL rows including the TOTAL rows, you should get 0 at the TOTAL rows.

This is what my workflow looks like:

I did not want to have to fight with the Excel Reader, so instead of having Knime read your Excel file, I simply copied a few lines from it and manually pasted them in a Table Creator.

The data includes these:
TOTAL REC. EM ATRASO
TOTAL REC. DO PERIODO
TOTAL CONSUMO DE AGUA
TOTAL CONSUMO DE LUZ
TOTAL HONORARIOS RECEBIDOS
TOTAL RECEITA DE CORREIO

and looks like this:

So my “working” column looks like this after doing the manipulation:

After the Moving Aggregation, the TOTAL rows should show 0, and the aggregation for the row right above TOTAL should have the same value as what the row TOTAL has:

In the end, I can just do a Row Filter on rows “TOTAL *”:

Of course, the only caveat here is that it relies on the fact that the aggregation starts at 0 (or -0) at the beginning of each “section”, meaning that if one of the totals is not correct, it will screw up the rest of the aggregation

But the reason why I like this method is cause it’s easy to display the results like this.

I can do the GroupBy method too:

As it is, it will produce this result:

You need some additional manipulations to present it in a nicer way.

And I purposely used another way other than Rank or Column Expressions to get a unique identifier per TOTAL row - in this case I used the Counter Generation node.

Here’s the workflow with both options: Aggregate rows above TOTAL.knwf (41.9 KB)

EDIT: Oh what the heck, it takes just a couple of minutes to do the additional manipulations:

Results:

Updated version of the workflow: Aggregate rows above TOTAL.knwf (49.3 KB)

[Bleck]

YO thank you very much and dont worry i am never in a hurry
and thank you for this fantastic explanation

[Daniel_Weikert]

@bruno29a

sorry for the delay, I’m quite busy during the day with work.

Considering your contribution here I though you “KNIME” here in the forum for living.
The running total (MA) with resetting by negative values is a great idea. Thanks for sharing.
However it only works as long as you really meet the value. If you have a delta then the running sum gets distorted because it does not reset without any grouping or am I missing something?
br

[bruno29a]

Hi @Daniel_Weikert , that is correct, and I mentioned it already:

@bruno29a

the only caveat here is that it relies on the fact that the aggregation starts at 0 (or -0) at the beginning of each “section”, meaning that if one of the totals is not correct, it will screw up the rest of the aggregation

It was just a fun way to do, but of course it had this flaw. That is why I provided another solution that’s more reliable.

[Daniel_Weikert]

Ah sorry my eye didn’t catch that