Join collection column with integer

[nojusas]

Hi,

So I have this problem where I need to join a column of type collection (‘List’) with a column of integers (‘SAP nr.’). The rows should join if a list (‘List’) contains the integer (‘SAP nr.’)

What would be the best way to do it? I tried doing a cross join, and then filtering out the rows where the list does not contain the integer, but that creates a huge table and is very inefficient time-wise.

[aworker]

Hi @nojusas and welcome to the KNIME forum

There is a very similar question to yours whose solution could be of help:

Join on partial string match, or Join if string is element of a larger string

Hi @Residentstiefel My suggestion here would be to convert the concatenated keys of first table into a collection (list) of values using the cell splitter but checking the option to generate a single column with a collection of values. Then I would use ungroup, do the joining and then group again if needed. The idea is here but if not clear enough, just tell me please and I’ll be happy to provide the workflow solution. Hope this helps. Best Ael

In essence, you need to ungroup first, do the joining and eventually groupby again to obtain the expected result.

Hope this helps.
Best

Ael

[nojusas]

Thanks, sounds less complex than crossjoining.

[aworker]

My pleasure to help and thanks @nojusas for validating the solution.

Best

Ael