Xlookup/Vlook-up Alternative in KNIME

[maya29]

Hi,

I am looking to achieve the following below but have been unsuccessful so far using Cell replacer, and Joiner. When done in excel it is a simple Xlook up formula or Vlookup.

Not sure how to get the Output data below from Input data below.

[gonhaddock]

Hello @maya29
You can achieve this task in two steps:

1. ‘Unpivoting’ node retaining your “Store” column
2. ‘String Manipulation’ node:
   join($Store$, $ColumnNames$)
   or
   joinSep("_", $Store$, $ColumnNames$)

BR

[maya29]

Thanks alot for your help. It worked for the most part except for the grouping segment.
I’d like to Group Store1 with Book1 and Store 2 with Book1, and then Store1 with Book 2 etc…Store 1 with Book 3.

I did manual table insert, and then joined the data via inner join. It works. But is there maybe a better way?

[Daniel_Weikert]

Joining each book with each store requires a crossjoin
br

[gonhaddock]

Ok, the workflow has to end up with something like this:

And after some tidy up, ending with your desired output.

20220126_unpivoting.knwf (22.4 KB)

BR

[maya29]

Thanks alot! That works.