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

Question

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:

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

Best,

Rachel

Join Question.xlsx

AngelosPachis · Accepted Answer

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

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.

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

Regards,

Angelos

DavidP · Accepted Answer

Hi @Rachelxyq

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