Hierarchical or Tiered Join

KNIME Analytics Platform
#1

Hey all!

I'm a relatively new user, and this is my first post here :D. I recently was given the challenge of doing a hierarchical (or tiered) join of two tables. The pseudo-flow would work like this

  1. Left join Primary Table and Secondary Table with certain field(s). Assign a Flag value of 1 for matching rows
  2. Any rows in Primary that did not join on first join: Join on another set of fields, and assign a Flag value of 2
  3. Any rows that did not join on second join: Join on another set of fields, and assign a Flag value of 3
  4. Any rows in Primary table that had no matches after the three joins, assign Flag value of 0

I was able to successfully build this in KNIME; see attached.  However, since I am relatively new to KNIME, I am wondering if there would be a more efficient, creative, or elaborate way to accomplish this.

Cheers!

#2

Hey,

in case you have to perform a large number of joins and if you can somehow describe the joining columns so that you can iterate through them, you can use a (recursive) loop.

Best,
Marc

#3

First of all, I love your nickname!  I could see iteration as an option in larger cases.  Perhaps reading a table containing data columns to join to feed a variable or something.  That's way beyond my KNIME skills at this point :D.

#4

Yes, that's how I would do it. ;-)

Good luck improving your skills!

Cheers,
Marc

1 Like