Knime - Vlookup with exact match

Hello everyone,
I have sample data (sheet:Dump) which looks like this (Attached the file as well):


The following has to be replicated in KNIME. The procedure is that I have to pivot the DUMP data and the new data looks like this (Attached as well):

vlookup test.xlsx (18.9 KB)
pWHBYp2tYxU8Zqd0WVKP.png)
Once the pivoting is done, I have to fetch the Source column from the Dump sheet into the Report sheet. The expected value shpuld be AEAE, but however, when I try the same using KNIME, I can find some discrepancy in the data. The joiner node returns more values than expected. I have attached my KNIME workflow as well:


vlookup test.knwf (16.9 KB)
Any help would be appreciated!
Thank you!

HI @Saishiyam,
there’s an important difference between the VLOOKUP function and your workflow: VLOOKUP returns a single value, Joiner+Duplicate Row Filter (in short: DRF) filter N>=1 unique rows coming from the join of two tables, based on a subset of their columns and a selection criterion. In your case the join returns three rows, identical in all columns except “Pearl Source ID” (ID), where two different values are present. If you don’t mind which ID is chosen, simply edit the DRF and move the ID column to the “Exclude” tab. If you want to choose - let’s say - the most recent row:

  • edit “Joiner” and include in the right table both Timestamp and ID
  • edit the DRF
    – exclude Timestamp and ID from the columns used to filter duplicates
    – edit the “Advanced” tab of the Duplicate and change the row selection criterion like this:

Hope these suggestions will help you

2 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.