Removing duplicates

[Rohan139]

Hi Peeps,

I have this input data. Basically I want to Map the Email to Mobile in table 1   , but the problem is when I will apply  join it on Mobile Column It will Create 2 rows Each for Both the ID's which is Obvious Behaviour of Joins .

TABLE1
ID	MOBILE
P1	9811…
P2	9811…

TABLE2
MOBILE	EMAIL
9811…	xyz@gmail.com
9811…	abc@yahoo.com

OUTPUT GENERATED
ID	MOBILE	EMAIL
P1	9811…	xyz@gmail.com
P1	9811…	abc@yahoo.com
P2	9811…	xyz@gmail.com
P2	9811…	abc@yahoo.com

But I want output to be  Like Below . so that Email should not repeat if it occurs once .

ID	MOBILE	EMAIL
P1	9811…	xyz@gmail.com
P2	9811…	abc@yahoo.com

Please suggest some way to do this. 

Thanks in Advance.

[DataNath]

Hey @Rohan139, if you add a unique tool after the join and tick ‘email’ as the unique field, does that remedy the issue?

[Rohan139]

ID	MOBILE	EMAIL	Test Done
P1	9811…	xyz@gmail.com	sugar
P1	9811…	xyz@gmail.com	thyroid
P2	9811…	abc@yahoo.com	Lipid
P2	9811…	abc@yahoo.com	Kidney

hey @DataNath , yes it would have work for me  but in current scenario if  I will apply the above logic it will only return single row.

[DataNath]

Oh, so in this case you’d want everything condensed to one row and all of the ‘Test Done’ records together?

If so, you could use a summarize tool - Group on ‘Mobile’, Group on ‘Email’ and then for ‘Test Done’ go select String > Concatenate.

[binuacs]

@Rohan139 your data is having duplicate mobile number ,thats why you are getting 4 rows while joining. are you expecting duplicate mobile numbers in your data? IF not you are use either join tool or find and replace to get your desired result

[binuacs]

@Rohan139 Another option if you have duplicate phone number use the join by record position option in the join tool

[Qiu]

@Rohan139 
I would do the same as @binuacs , mabe adding a sort tool to make sure the order?

And for your mobile column, is it a mobile number? so it would be longer and become unique maybe?

[Nezrin]

If the order of records is going to be the same, you could use "Join by record position". Else apply a sort before joining