# Handling with tables

Hi Everybody,

I'm in trouble with the following situation:

I got 2 tables, Table 1 got for every project a start year and an end year. the columns 2013-2019 are empty for the moment and need to be filled in.

Table 2 got values for project costs. Now want to join cost and year.

So, column "1" in table 2 is the "first cost value". Because project 1 starts in 2013 the first cost value stands for the year 2013 . So:

Project 1: --> 2013 = 32.256€

2014 = 21.569€ and so on... the last value (not 0) in a row of table2 is always the total, but this value is not relevant.

Table1
 Start End 2013 2014 2015 2016 2017 2018 2019 1 2013 2016 2 2016 2017 3 2013 2019 4 2015 2015 ... ... ...
Table2
 1 2 3 4 5 6 7 8 9 10 11 12 32.256 21.569 12.222 414.254 1.246.458 0 0 0 0 0 0 0 45.222 1.362 49.254 0 0 0 0 0 0 0 0 0 25.015 1.152 1.259 78.025 13.152 1.596 25.547 123.152 0 0 0 0 584.236 584236 ... ... ... ... ... ... ... ... ... ... ... ...

So at the end I want to have table1 like this:

Table1
 Start End 2013 2014 2015 2016 2017 2018 2019 1 2013 2016 32.256 21.569 12.222 414.254 2 2016 2017 45.222 1.362 3 2013 2019 25.015 1.152 1.259 78.025 13.152 1.596 25.547 4 2015 2015 584.236 ... ... ...

So, how can I realize this in Knime? Could I use the Jave Snippet or waht should I use?

It would be so nice if anybody van help me with this problem.

Thank you so much. Looking forward for any suggestions.

Vanny :)

Hi Vanny,

I was able to solve your use case by using the Lag Column node inside a loop, together with some transpositions and creative use of Flow Variables. See the attached workflow.

I have commented it throughout, but it may still be a bit complicated to understand. Some of the complexity comes from the fact that the Lag Column node cannot lag by 0, so I had to implement a workaround.

In case of doubts feel free to ask again here.

Cheers,
Marco.

Hi Marco,

that's really great!!! Thank you very much.

Cheers,

Vanny