Join Tool - Duplication of data

[harithad1]

Hi, I created a Summarize tool for creating a pivot customer wise  (Input 1) from various other inputs. Later used Join tool to get other data. But its creating multiple lines with same customer and same values and my balances are also duplicated because of that and giving a wrong output for aging. What should i do get avoid duplication. The region will be unique for the customer, however there are multiple lines in the input data. Below example is similar to my data.

[markcurry]

Hi @harithad1 

See the attached workflow.  With your sample data, if you don't need any of the data from the PO#, Order Qty, Unit Price fields, then you should Summarize the data after this, to just give you the list of customers and regions, prior to joining it with the rest of your data.  

In your example, Customer Number 100, Customer A is regional Asia and Europe, so I have output this as Asia / Europe.  Similarly Customer C is in Africa and Australia.  To handle this I firstly used the Summarize to get the customer numbers and regions, then because customers had multiple regions, I used the Summarize tool again, but concatenated the regions.

Another option you can use instead of the Join tool is the Find and Replace tool, but instead of Replacing you want to append your extra fields.  I hope that helps,

Mark