Alteryx Designer Discussions

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

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

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

 

Alteryx Certified Partner

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

 

16 - Nebula
16 - Nebula

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