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.
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