How to Remove Duplicates/Multiple Same Values in a "Joined" table

[Ruru]

Hi Everyone,

I have a query with regards to duplicate or “multiple same values” generated after using the JOINER node.

I have an Accounts Payable (AP)- One Table and Accounts Receivable (AR) -One Table.
I was trying to look up the “invoice number” of AP items from AR table. And so, I used the “left joiner” function.

I understand that JOINER node generates ALL values present in the lower table to the upper table based on the “concatenation” column, and in my case, the AR table have multiple values (invoice numbers) per my “concatenation” column. So, ALL of those values were generated by the JOINER node in the output table giving me duplicate/multiple rows now.

Is there a way I can massively remove the other other rows and at the same time CHOOSE only a single row to retain per set of multiple values?

I want to retain the rows which have “ZERO” differences only and remove the rest.
My concantenation column is “ARCompany&Customer&Reference”.

Thank you so much for those who will answer.

Here’s my sample:

[ScottF]

You could try the Duplicate Row Filter for this type of operation, although you would have to be a little careful about which duplicates to remove.

To be extra safe, you could just calculate the DIFF field after the join using a Math Formula node, and then use a Row Filter to remove records where the DIFF is not equal to zero.

[Ruru]

Make sense! Thanks for the prompt response, @ScottF! I will try this.

Will update u with the outcome,

[Ruru]

Hi @ScottF! It actually worked, thanks again. Tho, I now have additional conditions to input. Would you know how can I filter the values which have the least difference or “nearest” to zero difference? coz not all of the values are exactly equal to zero.

I tried using the duplicate filter column again and tick that “minimum” function to retain those items with the least difference but I guess it’s not ideal coz i have the Amount differences in both positive and negative so even though I want to get the item with difference of “+0.1”, the output table generated a larger number in negative values which is -425,901.

I am still trying to find another node that can help me. Do you have any idea tho? Thanks in advance!

[Daniel_Weikert]

Why not building a unique identifier? Maybe I am missing something

[ScottF]

@Ruru

Would you know how can I filter the values which have the least difference or “nearest” to zero difference?

This sounds like a case for the GroupBy node - set your groups appropriately, and use the Minimum aggregation on the calculated difference.

If you are still having trouble, feel free to post your workflow in progress and a sample dataset.