Calculating new column (1) as input for new calculated column (2) as input for new calcluated column (up to ~60) etc.

Hello KNIME forum,

I have a - probably - simple problem to solve but am not able to find a solution by myself. I have 2 base columns 1) 2019-01-01 and 2) CAGR (fix) from which I need to generate an extrapolation of new columns; i.e.
2019-02-01 = 2019-01-01 x CAGR;
2019-03-01 = 2019-02-01 x CAGR;
etc.
2024-12-01 = 2024-11-01 x CAGR

Any idea on how to best generate these new columns? An example input file is attached.
Loop for calculating new column as input for another new calculcated column.zip (33.6 KB)

Many thanks in advance and best regards,
Isabell

Looping is definitely the direction to go on this!
The important node being the Loop End(Column Append) node. This will let you keep adding on columns instead of rows.

After that you have 2 things left to solve. Properly naming the columns and properly applying your CAGR.

  • For the column name you can extract the header, convert that string to a date, then shift the date forward to the new month. This will give you the new month as a date that you can turn into a flow variable and use in the loop to rename the new column. I think it’s easiest to do this by using the current iteration # of the loop as the number of months to jump ahead from start.

  • For applying the CAGR, I’d suggest a similar approach to above, instead of recursively referencing the previous month try a math formula node that is: START * CAGR ^# where # is the iteration number of the loop.

Here’s an example of how you could do this.
There’s to much to say about the flow in one post so let me know if this example works for you or if you’ve got any more questions.

Happy to chat about it!

CAGR Data.knwf (27.3 KB)

5 Likes

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