i´m currently stuck at a project and would clal myself an KNIME Beginner .
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.
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
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.