Add info to rows from other table

Hi, I'm new and am looking for a solution to insert row information from a second table based on cell value.

I have a small table that needs additional columns PersonID and Home created and filled in. The values are in a large second table that may contain zero, one or more lines that match with the given email address. The Home column always contains the same value for each PersonID. For all persons that are not found in the second table, I would need to fill in the PersonID with a sequence number, from e.g. 99990000 onwards.

Would KNIME support this or would I be better off manipulating in Excel? Or create a database?

File table1:

Mail address

company

job

john@mail.com

CleanFast

Cleaner

Beverly@mail.com

CleanFast

Super

May@mail.com

Care&Share

Nurse

 

File table2 (large file)

PersonID

Mail address

company

job

home

1234

john@mail.com

Lean&Clean

Assistant

London

1234

john@mail.com

WeCleanIt

Super

London

6789

May@mail.com

Coddle

Nurse

Cardiff

 

Looked for result:

PersonID

Mail address

company

job

home

1234

john@mail.com

Cleanfast

Cleaner

London

99990001

Beverly@mail.com

CleanFast

Super

 

6789

May@mail.com

Care&Share

Nurse

Cardiff

Hi houtje,

KNIME is perfectly suited for the task.

You just need to perform an outer join (with the Joiner node) between your tables and use a Java Snippet (simple) to go through the lines with missing PersonID and assign the progressive Id to them. Since the process may create some duplicates, depending on the content of your larger table, you may also want to clean them up using a Groupby node. The RowID node simply fixes the row Id's after the join.

See the attached workflow.

Cheers,
Marco.

This is brilliant, and the example makes it really easy to apply that to my situation. Thank you so much!