Let’s talk Alteryx Copilot. Join the live AMA event to connect with the Alteryx team, ask questions, and hear how others are exploring what Copilot can do. Have Copilot questions? Ask here!
Start Free Trial

Alteryx Designer Desktop Discussions

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

Join Function/ How to avoid duplicates for one to many join

Rachelxyq
5 - Atom

Hi,

 

Hope you guys have a great day!

 

I was trying to join two tables together. But it was one to many relationship and I guess Alteryx join every possible values rather than one. In my case, this made it hard to know the total of sales units... So I'm looking for a way to count it once when they join. Please see the tables below:

Rachelxyq_0-1610095564026.png

 

Thanks for your time! I really appreciate if you can help me!😀

 

Best,

Rachel

 

2 REPLIES 2
AngelosPachis
16 - Nebula

Hi Rachel (@Rachelxyq)

 

The only thing I could think of is kind of a workaround.

 

So after your join, I have use a multi-row formula tool to assign a Row ID to each group of Customer ID, Product Category and Quarter

 

AngelosPachis_0-1610097366536.png

 

Then with a filter tool, I split the records in two parts, the one containing the first row of each group, and the other the duplicate records, which you want to set to zero. I did that with a formula tool and brought the two streams back together.

 

AngelosPachis_1-1610097488565.png

 

Hope that helps, let me know if it worked for you.

 

Regards,

 

Angelos

 

DavidP
17 - Castor
17 - Castor

Hi @Rachelxyq 

 

This is similar to @AngelosPachis idea, but use a tile tool to identify the 1st instance of a 1 to many relationship.

 

DavidP_1-1610101039669.png

 

Labels
Top Solution Authors