Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Complex duplicates

Belinda1
5 - Atom

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

 

Belinda1_0-1605624659333.png

 

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

 

Belinda1_1-1605624780406.png

 

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

3 REPLIES 3
PhilipMannering
16 - Nebula
16 - Nebula

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
5 - Atom

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
16 - Nebula
16 - Nebula

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

Labels