Multiple matching Rows for VLOOKUP

Hey there,

i´m currently stuck at a project and would clal myself an KNIME Beginner :slight_smile: .
Im evaluating sales orders and Invoices for dashboards.
I have an ID for every position of the sales order like -- and got the same reference for the invoice. Therefore i used the VLOOKUP and thought everything is fine.

Now my Issue is, that when the invoice is cancelled, there is a second enrty in this ID with the negative number. But VLOOKUP only can pick the first or the last value, but not append the row if there is a second value found.

Does anyone knows how to append the double entries but still keep the function of the VLOOKUP ?
I would like to add columns that match the ID 1:1 and append any additional columns for the same ID.

Thanks for any Help
Yannick

Hi @Yannick_Jasper

Are the ID’s always the same but just it’s negative equivelant, like 12345 and -12345? In that case I would apply the abs() function in a Math Formula node to normalize them. If you then apply a left outer join or inner join you should be able to retrieve both records.

But an anonymized example of your input and expected output in a workable format would go a long way here, to avoid a lot of guessing :wink:

hey @ArjenEX

yes the ID is the same but the value is negative.
So for the Data Example. I have Table 1 with the sales orders as:

Vertriebs-Auftragsart Vertriebs-Auftragsnummer Positionsnummer Auftraggeber Gesamtmenge Netto-Gesamtbetrag (Belegwährung) Rechnungsempfänger
100 13067547 10.004 2014723 1,00 49,50 2076495

and the correcponing invoice Data looks like:

Rechnungsempfänger Ausgangsrechnungsart Ausgangsrechnungsnummer Belegdatum Nettobetrag (Belegwährung) Rechnungsbelegempfänger Vertriebs-Auftragsnummer Vertriebs-Auftragsposition Vertriebs-Auftragsart
2076495 100 31054074 20.01.2022 49,50 2076495 13067547 10.004 100
2076495 100 91006283 07.02.2022 -49,50 2076495 13067547 10.004 100

I want to append all the Columns from the Second List based on the first list matching the ID, also when there are 2 entries found for the same ID.

Hope that eliminates guessing :slight_smile:

Have mercy with me for not correcly naming all the columns but if you do an innner or left outer join on “sales.rechnungsempfänger” = “invoices.rechnungsempfänger” and then include all the columns from the invoice table, you should be able to get your desired result.

Joiner

Column Selection

1 Like

I will have a look on your suggestion, but for now this might do the trick :wink:
Thank you very much for the quick response

1 Like

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