Complex duplicates

[Belinda1]

Hi All

i have changed accounting systems and as such i would want to check if the numbers have migrated correctly however i have duplicates on both sets of data.

I have used the joining tool to come up with one document that matches the old account numbers to the new ones but i have duplicates on both sets of data.

, see below 

Duplicates in the old system as i have accounts that have been split into various accounts in the new account

then on the other hand i have multiple accounts in the old system that have been combined into one account so when i perform my comparison there are duplicates again , see below

Is there a formula i can use that will keep one amount and for the rest of duplicate account numbers put a zero ?

[PhilipMannering]

Hi @Belinda1 

It's difficult to answer this without knowing where it's duplicated.

In general you can use the Unique Tool or Summarize Tool (and group by) to "de-duplicate". And then do your Join.

If you provide some tables (maybe text inputs in a workflow) with dummy data and desired outcome, I'm sure somebody will assist. 

[Belinda1]

Hi @PhilipMannering thanks for replying .

I have tried the unique tool but it doesn't work as though there are duplicates there are corresponding unique entries.

On the summarise one as i have duplicates instead of returning that account balance it will return the total of all duplicates

[PhilipMannering]

Hey @Belinda1,

Not sure I understand the first point without an example of what you mean. The unique tool should work if configured right.

For the Summarize Tool, I'm guessing that you do a sum of the Account Balance. You could do another group by, take the min, max, first, last or average to get the correct value. Just make sure you don't group by whatever is causing the duplicates. Keep all measures aggregated in a way that takes a single value and not sum.

Hope this helps. As always, feel free to post dummy data with desired outcome for more practical advice!

Thanks,

Philip