# JOINER/VLOOKUP DATA

hello, I have 2 questions

1. how can I JOINER/VLOOKUP my data, but I want the output like this

but the output i got like this

so KNIME take the latest data when do joiner.

1. plus, how can I define which data that I want to show in the output? let say I want 1.227.935,00, so the Credit Column will be showed like this
Rv number | Credit
CATMTM/0044/06/22 | 1.227.935,00
CATMTM/0044/06/22 | 1.227.935,00
CATMTM/0044/06/22 | 1.227.935,00

Hi @takdirzd , are you using KNIME 5.1.x ? If so, have you tried the Value Lookup node instead of the Joiner. It has an option to deal with Multiple matches, which I believe should be able to help you here:

If you are using KNIME 4.x, one option is to use a Duplicate Row Filter on the â€ślookup tableâ€ť first, having it detect duplicates just on the RV number column, and youâ€™d tell it to keep first/last as appropriate. You would then join on that.

1 Like

i use version 4.0xx, okay

but how about my quesiton number 1?

Hi @takdirzd, I think youâ€™ll need to give more background (e.g. input data and rules) for Q1 for somebody to be able to answer. You havenâ€™t given us much to go on

this is the example for my question, 2 tables in the left is the input, and the right one is the output

///

but I want the output like this. this is just example that i want to show you what i mean

Hi @takdirzd, I can see at least 2 ways (there may be others) that I could arrive at your â€śrequired outputâ€ť based on your input tables:

1. The column5 values are simply assigned by matching Row ID between your input tables.

or

1. The column5 values are assigned by matching â€śCodeâ€ť, and the sequential position of â€śstuff 1â€ť within groupings of â€śCodeâ€ť matching to the same sequential position in the second table.

But whilst I would assume that option (2) is more likely than option (1), I prefer not to assume, and you may actually have a different rule. So please can you say the basis on which you are wanting to derive the output table.

Also, could you upload the sample workflow you have as then people can more easily use this rather than having to type in the example from scratch.

That said, on KNIME 4.x, if it is option (2) above, the following should be adaptable to your needs:

forum_project_74732.knwf (14.3 KB)

This uses a counter to simply provide a sequence number that can then be used as a an ascending â€śranking attributeâ€ť used by the Rank node, so it groups by code with each line being given a â€śrankâ€ť value.

The joiner can then match on code and position (rank) and return the columns required

2 Likes

If you convert your number to a string, you could do a groupby in your second table and then choose first or last. This can then be joined to the first table
br

1 Like

it works, so it depends the row order right?

but i have another cases like this

my output is like this, that I using your way,

but the excel file output is like this

so this is wrong, but I ask the creator of the excel file, then she said that she did it manually, and have to switch it manually.

so I would say like, this way can not works if, lets say we have 2 files, the first one is ANDY, ANTON. the second is ANTON, ANDY. so from that, the row order is different each other.

Hi @takdirzd ,

As I explained, yes it currently works on position, because that is the only way I found that made sense with the screenshots you posted.

Now, it appears, you â€śactually have a different ruleâ€ť because you also have new information that wasnâ€™t present before - Cust Name?

With this new information you can (probably) remove all of the additional stuff with â€ścounter generationâ€ť and â€śrankâ€ť which had to be included to find something to join on (row sequence), and you can then try including â€śCust Nameâ€ť (along with Code) in the joiner node.

I hope that then works, but even now I am forced to make assumptions because you may come up with another example with different data again. Better if you upload a realistic and complete sample of your data (not just screenshots), and an explanation of the rules as this saves a lot of time.

1 Like

yeah im sorry for the missing information, but I add that â€śCust Nameâ€ť after discuss with the users, that maybe the Cust Name column can be the solution to get output I want. it works very well when adding â€śCust Nameâ€ť as the key as you say. but sadly itâ€™s requires much efforts because my Cust Name column is very â€śdirtyâ€ť.

Thank you!

1 Like

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