combining 2 tables over the same/next smaller value

Hey Guys!

I'm relatively new to knime and I have a problem with appending 2 tables. The tables need to be combined over the years. In one table, I have all the years, but in the second I just have some years. When the year is missing in the second table, it should take the next earlier year. Both year values are numbers, so taking the next smaller value would do the job:

Table 1

year  value

1990  1,5

1991  1,7

1992  1,2

1993  1,4

1994  1,5

 

Table 2

year  value

1990  5

1993  6

 

New table:

year  value  year(tab2)  value(tab2)

1990  1,5  1990  5

1991  1,7  1990  5

1992  1,2  1990  5

1993  1,4  1993  6

1994  1,5  1993  6

 

I have tried both the rule enigne and the math formula node, but I can't find anything to choose the next smaller value.

Thanks a lot for helping!!

Hi!

You can accomplish your transformation with:

1. a Left Outer Join performed with the Joiner node. This will create missing values for the years in Table 1 where there is no match from Table 2. Make sure you include all columns from both tables.

2. The output of the Joiner node needs to be followed by a sorting over the year column from Table 1. This is needed for the next and final step.

3. At this point you can use a Missing Value node to fill in the missing values using "Previous Value" as the option. This should apply to both Year (#1) and Value (#1) aka the two columns joined from Table 2.

Give it a try and report here in case you find issues with it.

Cheers,
Marco.

hm, this doesn't  really help. I think I forgot something. the Values in the bottom table are not "unique".

So there are a number of rows with the same year, but a different value. more like this:
 

Table 1

year value
1990 1,5
1991 1,7
1992 1,2
1993 1,4
1994 1,5

Table 2

year valueclass value
1990 1 5
1990 2 6
1993 1 7
1993 2 8

 

New table:

year value year(tab2) valueclass(tab2) value(tab2)
1990 1,5 1990 1 5
1990 1,5 1990 2 6
1990 1,7 1991 1 5
1990 1,7 1991 2 6
1990 1,2 1992 1 5
1990 1,2 1992 2 6
1993 1,4 1993 1 7
1993 1,4 1993 2 8
1993 1,5 1994 1 7
1993 1,5 1994 2 8

 

In this case, unfortunately, a left outter join doesn't work, because the valueclasses of table 2 wouldn't be transfered to the new table.

 

 

Sorry, I think I missed something.

You have table1 and table2 and want to get the new table?

Than you join on the year with inner join and your done?

the problem is, I need all the values of both Tables in the new table, but the original two tables don't always have a column in common. The join should be over the year, and in the second table I have all the years (1990, 1991, 1992, 1993, 1994), in the first I just have some years (1990, 1993). If a year from table two isn't represented in table one, it should take the next earlier year.

e.g. with the numbers above: in table two there is no year 1991, so for the join to table one, I would need the next earlier year in table two, wich is 1990 to be combined with the 1991 from table 1.

Tab 1 --> Tab 2

1990 --> 1990

1991 --> 1990  <--

1992 --> 1990

1993 --> 1993

1994 --> 1993

 

The next Problem is, that I don't just have one row per year in the Table two. I have multible rows, with different valueclasses, from wich all need to be combined with the appropriate year in table one. 

To put it without data: I have a (defined) number of rows with the same year value in table two, wich need to be joined with a variing number of rows of table one. In Table two I only have some years, in table one I have all the years. But each year of table one has to have a join to the (defined) number of rows of one year value of the table two in a way, that the year is either the same or the next earlier (smaller) year.

I put a scheme as an attachement to show you what I want to achieve.

Does no one know a solution to this? I'm still stuck with this problem and can't seem to figure out a solution.

Thanks to anyone who is trying to help!

Hey,

if you are still struggling with this problem, here is a solution, based on Marco's.

  1. Like Marco described, Left Outer Join, but remove the value from table 2, just use the year from table 2. (Make sure that both joining columns are there, your table needs to contain year from table 1 and year (#1) from table 2.
  2. Identical
  3. Identical
  4. Now you should have a table where each year from table 1 appears once and has a year from table 2 according to your contraints. Now you need a Joiner to do a Full Outer Join on this new table and table 2. Joining columns are year (#1) from the new table and year from table 2.

Best,
Ferry 

It works perfektly, thanks a lot!!!