Double data showing in result

[Maheshp]

Hi Team,

We have two files with same data but when we are joining/ vlook up then result is showing with double transactions, can you please check and let us know the reason and please provide the correct solution.

For example, in file 1 we have data like below.

Submitted Sales Amount	Unique Code
64.9	00039864.9
64.9	00039864.9

In second file we have same data as above.

but after using join tool data is coming in duplicacy " highlighted in red".

Amount in doc. curr.	Unique Code	Submitted Sales Amount	Right_Unique Code
76.95	00260R76.95	76.95	00260R76.95
76.95	00260R76.95	76.95	00260R76.95
76.95	00260R76.95	76.95	00260R76.95
76.95	00260R76.95	76.95	00260R76.95

File attached for your info.

Regards,

Mahesh

[Manoj_k]

Hi Maheshhp, instead of joining the data try union tool to merge the data. 

[Maheshp]

Thanks for your reply! if i use a union then it will not work as vlook up, i mean i need to value and unique code in front.

For example:- file 2 data come in front of file 1 as it is so that i can make formula of + - . 

[Hammad_Rashid]

It seems like the issue might be related to the way the join operation is being performed in Alteryx. When you perform a join operation, the result will contain all possible combinations of matching records from both files, which could lead to duplicates.

To resolve this issue and have a clean result without duplicates, you can follow these steps:

1. Check for Duplicate Data: Before performing the join, ensure that there are no duplicate records in each of your input files. It seems like there might be duplicates in your example data, and eliminating them beforehand can prevent duplication in the join result.

2. Use Unique Identifier: Make sure you are using a unique identifier to join the files. In your case, it seems like "Unique Code" should be the unique identifier. Ensure that you are using this field for the join.

3. Use a Unique Tool: You can use the "Unique" tool in Alteryx to remove duplicate records from each file before performing the join. This tool will keep only the unique records based on the specified key (Unique Code in your case).

4. Join Configuration: When configuring the join tool, ensure that you are selecting the correct join type. Depending on your requirements, you might want to use an inner join, left join, or another type of join.

5. Use a Sort Tool: Before performing the join, it can be helpful to sort both input datasets based on the unique identifier. This can help in improving the performance of the join operation.

6. Inspect Data: After the join, use the Browse tool to inspect the results and verify that the data looks as expected without duplicates.

Here's a possible workflow:

• Use a Unique tool on each input to remove duplicate records based on the "Unique Code."
• Use a Sort tool on both inputs to sort them based on the "Unique Code."
• Use the Join tool to combine the sorted and unique datasets based on the "Unique Code."
• Use a Browse tool to inspect the results and ensure there are no duplicates.

By following these steps, you should be able to perform the join without encountering duplicate transactions in the result.

[JosephSerpis · Accepted solution]

Hi @Maheshp looking at your data it will create a many to many join as for example your shared in your quesiton the data with unique code 00039864.9 has two entries in file 1 and also in file 2 so it producing 4 records when joined on unique code. If this is not the behaviour you will need to have an addtional field to join on to create a 1 to 1 join or you need to remove duplicates in your data before the join. You can try rectify the duplicates post join in Alteryx however the methods I mentioned are the best practises. I mocked up an approach which demonstrates how to deal with the duplicates post join.