Value Lookup, loop or other nodes?

[Kazimierz]

Hello KNIMErs,

I’m finalizing my workflow and I feel Value Lookup node should be used. Probably, it should be located inside a loop. However, I can’t build the correct workflow for this. The below example replicates my challenge.

Sample Data Table looks like this:

Sample Dictionary Table looks like this:

Expected Output Table should base on data table, contain extra column and look like this:

Sample workflow looks like this:

Of course, real tables contains more rows, more columns, and there are variety of patterns being used for column names.

How should the sample workflow look like to generate expected output table from input tables?

Happy KNIMEing,
Kaz

[gonhaddock]

Hello @Kazimierz
You can test to shape in long format your ‘Dictionary Table’ with Unpivot()

Afterwards an inner Join with double matching criteria ($Index$ and $Attribute name$) should deliver your expected result.

BR

[takbb]

Hi @Kazimierz ,

@gonhaddock 's solution is exactly right, and is what I was building as a solution. The unpivoting of the dictionary table is crucial because the lookups do not join “across” the columns but instead match by a key found in a particular “key” column (or columns in the case of Joiner).

Joiner is necessary here because you are joining based on multiple column values.
You could instead use Value Lookup but you would have to first generate a single “key” column based on the concatenation of the two attributes that you are trying to lookup.

Lookup values in pivoted table.knwf (92.4 KB)

The Table Manipulation node is simply there to reorder and rename the columns.

The upper example in this workflow is essentially what @gonhaddock is describing, so if this works for you then please mark his response as the “solution”.

[Kazimierz]

@gonhaddock @takbb this is unfair😉

Both workflows generate expected output, both replies provide solutions, but I can mark only one reply as THE solution… I’m following @takbb’s suggestion then.

I’ve used to use aggregated / concatenated / joined strings to create new, unique strings, however this didn’t come into my mind here.

Thank you both for your support!

[gonhaddock]

thank you @Kazimierz
No worries as none of us really cares about solution counting. Do we? besides @takbb is a gentleman

As you see some user contributions trend to provide a full deployed workflows. In my case, when I can spot the solution at first sigh; I prefer to provide a brief description to make you think and explore node configurations… this is the way I personally best learn with.

Then extend the contribution on different degree if needed (snapshots, codes, and finally a workflow). This method I think that also help forum navigation reading.

However different people have different learning processes as well…

Enjoy KNIM(e)ing