Use value from previous row to calculate next row

Hello

I have following problem and can’t find a solution.

My datatable has two fixed datasets (column 1 and column 2). Every value from column 2 must be multiplied with values of column 1.

so for example
C2 = A2*B2
C3 =(A2 * B2) * A3
C4 = (A2 * B2) * A3) * A4

I have tried the recursive loop node but failed. Can someone help? Many thanks!!

The datasets and results look like this at the end
2023-10-18 22_04_20-Knime HFC - Excel

Hi @andreasdepta , welcome to the KNIME community

So at the end of this you are always going to end up with approximately the same number of columns as you have rows?

I presume that the number of rows can vary?

yep thats correct but the number of rows won’t change for now

Hi @andreasdepta

Welcome to KNIME Forum. I think I got it working. My outcome does not match exactly your outcome. I presume that your numbers in column 1 and 2 have more decimals than shown. That said, the trick that makes this work is to use to Moving Aggregation node with the function “product” within the Cumulative Computation. See


gr. Hans

4 Likes

Nice one @HansS , and doubly well done on interpreting a workable pattern because I was stuck on determining the value of D3 from the question :wink:

btw I plugged your values into Excel and I concur with your result based on interpretation of the rules.

So why was I stuck on D3?
… because the example provided said that the entry for C2 (the first entry of Column C) =A2 * B2, and to me, that meant that the first entry of D3 should also be the product of two (or more?) other values. Perhaps it would therefore be the product of A3 and B3, but the only way to make the example work was to always either simply take the value in Bn (which is I believe what you opted for), or always multiply Bn by the 1.00 in cell A2. Neither of those options are described in the question.

I have perhaps missed something, but I don’t think the value of D3 can be clearly inferred from the question with the example given.

So @andreasdepta, could you confirm how D3 should be determined, and whether your description of C2=A2*B2 is correct? For example, I would be interested to know what the expected result would be if the value in cell A2 were something other than 1.

1 Like

@HansS thanks for your solution - it works perfectly! and yes you are right with the decimals causing some smaller deviations - sorry for that :slight_smile:

However, I just would like to understand why the lower part using the rowID and rule-based row filter is really needed. there seems to be no effect on the outcome, or?

2 Likes

@takbb sorry for not being precise enough. I definitely see your point. D3 indeed must equal to B3 , whereas D4 = D3 *A4 and so on. Thanks for your help.

Screenshot 2023-10-19 224530

2 Likes

Hi @andreasdepta

I’m glad I could help you with my solution. You’re right. the part with the three nodes (RowID, Rule-based Row Filter and Column Filter) is redundant. It is a remainder of my search for the solution.

Please mark the post as the solution with the :white_check_mark: when you get around to it. It helps point future user’s searches toward correct answers to their questions, and helps forum admins to avoid questions slipping through the cracks and going unanswered.

3 Likes

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