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.
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.
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.
if you are still struggling with this problem, here is a solution, based on Marco's.
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.
Identical
Identical
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.