Handling with tables

KNIME Analytics Platform
1

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 :)

2

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.

3

Hi Marco,

 

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

 

Cheers,

 

Vanny