Joiner / Vlook-up in KNIME question

[CK24_SH]

Hi,

I am quite new to KNIME and for the last hours I’ve been going crazy.
I have two tables:

Table A

Table B

I want to join these 2 tables based on the country. In Excel I would VLookup the other columns based on the Country column. In KNIME, I’ve tried using Joiner but I am getting almost 800.000 rows when in reality it should be the same number of rows as in Table A.

Any idea what I am doing wrong?

Thank you!

[izaychik63]

Your table B may have duplicates by Country, say with different zones or region, so on.
Also take in account that Vlookup most likely corresponds to Left Outer Join of table A with table B.

[JanDuo]

Hi @CK24_SH

In a Joiner you most likely want to choose a column (or columns) which make a row unique in a table. If you join on non-unique columns you get an exponentially growing result-table.
If a value of Country appears 10x in table A and 40x in table B this will give you 400 rows in the result.

If possible you can use a GroupBy node to make a table with unique rows for a Country, before the join is made.

[mlauber71]

If you want to deal with duplicates this article might be for you

School of duplicates - and how to deal with them

Dealing with duplicates is a constant theme with data scientist. And a lot of things can go wrong. The easiest ways to deal with them is SQL’s GROUP BY or DISTINCT. Just get rid of them and be done. But as this examples might demonstrate this might not always be the best option. Even if your data provider swears your combined IDs are unique especially in Big Data scenarios there might still be lurking some muddy duplicates and you should still be able to deal with them. And you should be able t…

[CK24_SH]

Thank you, this worked.

[CK24_SH]

Thank you very much