Join of duplicates

[nicolas567893]

Dear all,

I have a challenge that you may help me to resolve.

I want to link two files with the "Join" tool. The problem is that there are duplicates...

The left source file (1 column) shows how many IDs were processed, in this case three times the ID "123456". The available information is limited to the "ID", i.e. I cannot distinguish the individual lines from each other - there are several duplicates:

ID

123456

123456

123456

…

The right source file (two columns) shows a list of IDs with the respective gift packaging fee. In the right file, the ID "123456" is only listed twice. This means that two shipments with the ID "123456" were gift wrapped and one shipment was not. Therefore, the ID only appears twice instead of three times:

ID	Gift Wrap Fee
123456	5
123456	5
…	…

The desired result looks like this:

ID	Gift Wrap Fee
123456	5
123456	5
123456	[Null]
…	…

However, if I use a "Join" tool, the following table results (see below). It is clear to me that it is because of the duplicates.

ID	Gift Wrap Fee
123456	5
123456	5
123456	5
123456	5
123456	5
123456	5
…	…

I "solved" the problem by switching numerous "Unique" and "Join" tools one after the other. However, there is a danger that I have not built in enough "loops" if, for example, an ID appears 100 times...

Do you have a solution / idea for me?

Many thanks in advance for your help!

Kind regards
Nicolas

[JosephSerpis · Accepted solution]

Hi @nicolas567893 what I do when I know there are duplicates in my data when i'm joining is I create a rank based upon that field using the multi row tool. I've mocked up a workflow let me know what you think? 

[Cal_A]

Can't go through a proper solution right now.  But you could try numbering each instance of each ID.  In this way you create a unique reference (in each table) than can be used for the join.  Once you have this, the rest is easy.  A few different ways to create the instance numbering e.g. running total, but there must be a more elegant way.

ID	instance			ID	instance	Giftwrap?
6546	1			6546	1	$5
6546	2			6546	2	$5
6546	3			123123	1	$5
123123	1		vs	123123	2	$5
123123	2			123123	3	$5
123123	3
123123	4