How to efficiently split a table based on another table?

Hello,

I’m a real beginner with Knime and I’m still discovering the various existing nodes. So I may I’ve missed something.

I’m trying to split a dataset A in 2 parts based on values in another dataset B.
The first dataset is my article table and provides information on articles for each company of my database.
There are 2 main columns in this dataset, called Article and Company.
Something similar to:
article | Company | A1 | A2 | …
ART1 | COMP1 | x11 | x12 | …
ART1 | COMP2 | x21 | x22 | …
ART1 | COMP3 | x31 | x32 | …
ART2 | COMP1 | x41 | x42 | …
ART3 | COMP3 | x51 | x52 | …
ART3 | COMP4 | x61 | x62 | …

The 2nd dataset provides a list of articles for several companies, with 2 columns called ART and COMP. Something similar to:
ART | COMP
ART1 | COMP1
ART1 | COMP3
ART2 | COMP1

I would like to find the sets (ART, COMP) of the 2nd dataset in the 1st dataset and split it in 2 parts.
So the ouputs should look like:
article | Company | A1 | A2 | …
ART1 | COMP1 | x11 | x12 | …
ART1 | COMP3 | x31 | x32 | …
ART2 | COMP1 | x41 | x42 | …
and
article | Company | A1 | A2 | …
ART1 | COMP2 | x21 | x22 | …
ART3 | COMP3 | x51 | x52 | …
ART3 | COMP4 | x61 | x62 | …

For the first output, the easier way is to use a joiner node. Quite easy and fast to implement.
But I don’t have the 2nd output so easily in this case.

Another solution is to use the Rule-based Row Splitter (Dictionnary), but in this case I have to use a String Manipulation node for each dataset, in order to create a new column that concatenate the article and company columns, and in order to create the rules for the splitter node. It’s not as easy as the joiner node, but it’s manageable.
Unfortunately, this process is very long: several hours for my file (650 000 rows and 256 columns), while the joiner requires less than 1 minute (but I don’t have the 2nd output in this case).

What is the optimal way to split such table?

Thank you in advance!

Look at


or

to split original set using the first one you can get fast.

4 Likes

This is exactly what I was looking for :smiley:

Thanks a lot!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.