Partial match between 2 tables

Hey All,

I’m a new knime user and I’m struggling with doing a partial match between 2 tables.

In ‘table 1’ I only have 1 column; in ‘table 2’ there are 2 columns.

Table 1
Type

Premium customer
Plus prospect
Platina buyer

Table 2
Grade ; Level

Premium ; C-level
Plus ; B-level
Platina ;A-level

I would like to do a partial match based on the values that can be found in ‘Table 1’ and append that with the Grade and Level info.

Can someone help me out with this?

Many thanks,

Hi,
you could write them to in memory dbs like H2 and do a Join with “LIKE” operator but it probably would be simpler to use cell splitter node with space to create a new column in table 1 which has then Premium, Plus, Platina which you can then simply join using joiner node
br

1 Like

@meszaros_mate , I fully agree with @Daniel_Weikert here. That isn’t a straightforward “join” in most systems, whether it be KNIME or a database, and splitting the first word out in Table1 so you have an easily comparable column is the way to go.

But if you really do want to go down the “exotic” :wink: route, based on the H2 database but without having to know anything about the H2 database then take a look at my Joiner Components demo workflow

3 Likes

Your “Join Custom Condition” is probably my favorite community component! Using the temporary H2 databases for conditional SQL joins really simplifies difficult tasks and opens up new possibilities. Thanks again for the contribution!

1 Like

Thanks @iCFO , I really appreciate that, and also I really value your feedback that has helped refine and improve both this and other components.

1 Like

@takbb You’re the best. You figure out stuff that I couldn’t do in two lifetimes.

1 Like

Thanks @rfeigel, you’ll make me blush! Lol. You’re too kind. You do pretty good yourself, as one of the regular contributors of this great community, and none of us would be what we are without all of us!

Take a look at this workflow. It demonstrates 5 different “partial join” approaches including @takkb’s Join Like component. I posted it a while back for someone else. Some obviously work better than others but it shows you a number of techniques.

2 Likes

Thank you very much, I’m going to double-check them :slight_smile: