Joiner with "where" condition

KNIME Analytics Platform
#1

Hello,

this is my first time in the forum and also I'm a beginner with Knime.

I already started my first workflow but now I need to do a join that in sql I could write like this:

select  *  from Table1 a
join Table2 b
on a.segmento=b.segmento
and a.rating=b.rating
where b.period_id_t <= a.mesi_a_scadenza

Table 1 is a knime table

Table 2 is uploaded in Knime workflow by a Database Reader node.

I build the join using Joiner node (Data Manipulation section), but I could not set the where condition:

"b.period_id_t <= a.mesi_a_scadenza".

Is there someone that knows how to do it?

 

Thanks in advance.

Regards.

Claudia

 

 



 

1 Like
#2

Hi Claudia,

I don’t think that you can add this condition to the Joiner node directly. However, you can perform the join without this restriction first and then use a subsequent Row Filter node (I would probably use a Java Snippet Row Filter) to extract the rows that you are interested in.

This might be a bit inefficient if the condition reduces the result size significantly, but improves the readability of the process at the same time.

Hope this helps,
Nils

#3

Hello Nils,

thanks a lot for your answer. This is a good idea. I tried but after two hours the execution of the Joiner wasn't completed. It is like 350.000 rows multiplied by 360, then 126.000.000 rows in total, before the execution of the Row Filter node. I think the server doesn't support this dimension...

Maybe with a R node but I have to study how to do because I am new also of R language.

 Maybe I have to import two data frame, first for Table1 and second for Table2 and after join them using a loop (????).

Nils (or someone else) know how to do? Like name of the R node that accept two input?

Thans a lot.

Claudia

 

 

 

 

 

1 Like
#4

Put your data into a database using KNIME and then run the SQL on it. You could use SQLLite for this if no other database system is available.

#5

Hi Claudia

I'm not sure I understand the 120mm rows.

The join should be reducing the number of rows and not increasing them, so if 350,000 and 360 are the number of records of the two tables you would not get 120mm for the row filter.

I have attached a sample workflow, in case you are still trying.

David