Splitting a column. Then using split columns to pull values from a different table before rejoining

KNIME Analytics Platform
1

Hi everyone,
I’m hoping someone for some guidance on a scenario that I can’t quite get my head around a solution for.

I need to split a column (a variable number of times) based on the delimiter “+”, then use each column produced by the split to lookup a value. These values need to be concatenated for all relevant split columns into one RESULT column.

I’ve prepared an illustration to help explain (and included the data in an Excel file for ease of replication):

Table 1: has an ‘Instructions’ column. This contains the combination of fields that should be produced as the Result.

Please note, each individual field is separated by a “+” but there will be some rows that don’t contain “+” and therefore need to return only one value (There is also no limit to the number of “+” that can be in each row)

Table 2: is a mapping file created to lookup the instruction fields from Table 1, to the relevant rows in the column ‘Value Field’ in Table 3.

Table 3: contains the values we want to return (in the Value column) based on a lookup to the ‘Value field’ col.

:

image
image1485×272 9.54 KB

KNIME example 1.xlsx (10.0 KB)

Thanks very much in advance for any help you can provide - this one has got me going around in circles.

tp

2

How about this one?

I’m using:

  • Chunk loop for row wise looping over table 1
  • Cell splitting and pivoting for the instruction
  • Joining Table 2
  • Value Lookup for table 3 (can be done with joiner node as well)
  • grouping to create the unique concatenate (see seperator setting within Node)
1 Like
3

Hi @ActionAndi,

Thanks so much for your reply. I appreciate you taking the time.

I’ve imported the workflow into KNIME but I am getting the following error on the Joiner node. I can’t open the Joiner node either to see what your configuration was.

Do you know why i may be getting this error? (for info i am on version 5.2.3 and due to company guidelines I can’t get any later version currently).

image
image1326×388 68.3 KB

Thanks.
tp

4

Hmm, I don’t know.
Just grab a new joiner and select: ColumnValues for the left and Field for the right table.

5

@ActionAndi This is perfect, thanks so much. Really appreciate it :smiley: