Creating column based on rules from another reference table

KNIME Analytics Platform
1

Hi,

 

I am dealing with a column (messy column) that doesn’t have any structure. Something like that:

 

queen's school of business, University A, kingston, on k7l 3n6, canada

University B, alberta cb2 1ag, canada

University C, united kingdom

 

I thought on creating (by hand) a ‘reference table’ containing the universities and countries, as follows:

 

Univerisities      Country

University A       USA

University B       Canada

University C       UK

 

I would appreciate HELP to know if it is possible to create a column based on rules from the reference table. I mean, I would use the column ‘Universities’ as reference, and apply it on the column that doesn’t have a pattern (messy column). The rule would be something like that: if in any content of ‘messy column’ there is any content of column ‘Universities’, insert in columns beside the correspondent content from the table of reference.

 

The expected output:

Messy column                                                 New column 1              New column 2

University B, alberta cb2 1ag, canada            University B                 Canada

University C, united kingdom                          University C                  UK

 

Is Knime able to handle that?

 

Many thanks in advance,

Cadu

2

Hi Cadu

In KNIME 2.8.0. there is a new node called Wildcard Tagger in in the TextProcessing addon. I attached an example workflow. See below.

But you have to intall the text processing nodes via the Help menu.

 

Frank