Join 2 tables with interval parametres

I dont know, how can I join data with 2 tables. I have 1st table with ID and 2nd table with interval ID (First and Last).  

I need connet these 2 tables.

Do you have any idea?

Thank you for tip.

Hi premyslav,

In order to join two table in KNIME Analytics Platform you can use the Joiner node. The node joins two tables in a database-like way. The join is based on the joining columns of both tables. After an Inner Join only matching rows will show up in the output table. A Left Outer Join will fill up the columns that come from the bottom table with missing values if no matching row exists in the bottom table. Same for the Right Outer Join; It will fill up the columns from the top table with missing values if no matching row in the top table exists. instead, a Full Outer Join will fill up columns from both the top and bottom table with missing values if a row cannot be joined. 

In the example that you presented there is only one row that matches in both tables (first and second). So, if you use the inner join, the result will be to have only one row with 2371297 as ID. What kind of join would you like to perform? 

Best,

Vincenzo

Hi Vincenzo,

I want to join every rows from table 2. This Id must be between FirstId and LastID in table 1.

So for example I need connect from tab2 ID=2371312 with first row in tab1, because ID=2371312 is in interval from 2371297 to 2373154. 

In Excel use I form vlookup and I find the nearest first ID and then I test is it in interval. Maybe can be similir in Knime. I am a new in Knime.

I have one idea. It possible use Cross Joiner and try it is in interval. Do you have better idea? 

There is Filter Existing Rows node by Actian. It is not yet a part of KNIME but can be insatalled and works.

In whole Actian jet is not very sttable in KNIME.

Dear premyslav,

I believe joining is not the right approach to your problem.

I would rather bring both table's ID columns into one table via concatenate. Then you can sort and fill the missing Y values from the previous value.

The attached workflow illustrates this solution for your (simplified) example.

Best regards

Arne

Hey premyslav,

I understand what you want to achieve differently from arbe. I would solve this with a Rule Engine. Attached you will find a workflow demonstrating how to solve what I understand you want to do.

Best,
Ferry

Thank you very much for interesting ideas. I decrease time of calculate from 3 hours to 10 min.