Hi,
Would like to enquire advise any node to amend data from multiple column to 1 column then lookup number from excel to excel. As tried to use value lookup node, the result shows in image 3 where numbering only lookup into the first number but desired output is image 4.
Image 1: Excel 1
image 2: Excel 2
Image 3: output
Image 4: Desired output to be
Hello @TanYiPheng
You can test by transposing the Table2, and then a Left Outer Joiner afterwards.
BR
hi,
sorry for the less detail info, let me attach the image with more detail screenshot.
Image 1
image 2
Image 3: desired output
Hello @TanYiPheng,
you should use Pivot node (Value columns are BNRs, and Retained columns are others or just ones you need for later) followed by Joiner or Value Lookup.
Br,
Ivan
It would be helpful if you posted actual data rather than screenshots
Hi,
I tried to use pivot node to make BNRs into column but it shows error of ambiguous pivot/group column.
Hi,
DSO_testing_1.BOM excel is my original master data,
Example_1 is the data i want to refer into the master data above.
the desired output excel is the excel that i had produced where i try to build it in Knime.
Anyway node that i can use as in excel im able to lookup according to the column and combine as i want because BNR stands alone in each column, however in knime i unable to achieve same result. When i use value lookup, the BNR moves in all row together instead of column.
Desired output_28Jan26.xlsx (14.9 KB)
Example_1.xlsx (13.8 KB)
DSO_Testing_1. Bom.xlsx (12.5 KB)
I don’t think there is any way to produce your desired output from the input you provided. The former has 61 separate #BNRs and the latter only 21. Here’s a workflow which joins the data in your two input tables. It has some extra nodes for QA. While Knime is excellect for data cleaning, you would be well served if you could produce cleaner datasets. Your data is an absolute mess.
