extract duplicate data

[renjithpl]

Hi All,

i have a table like below

Sl No.	Matter ID	Matter Name	First Name	Last Name	MailID
1	447306	abc	Lenin	desotha	lenin.desotha.idea.com
2	447306	abc	Martin	hamp	martin.hamp.idea.com
3	447307	YYY	Lenin	desotha	lenin.desotha.idea.com

there will be duplicate/same mailid's for different MatterID,

I want to find where the mail ids are getting duplicates and get those Matterids.

Finally my output table should look like below:

Matter ID	Matter Name	MailID
447306	abc	lenin.desotha.idea.com
447307	YYY	lenin.desotha.idea.com

Any help?

[#2]

Renjith,

You need to have a logic which ones to remove. But this is how its done:

Temp:

Load SlNo.,MatterID,MatterName,FirstName,LastName,MailID, autonumber(MatterID,MailID) as MatterMailID from ...;

Join(Temp)

Load MatterID,Min(MatterMailID),1 as IncludeFlag Resident Temp;

Final:

Load SlNo.,MatterID,MatterName,FirstName,LastName,MailID as FilteredMailID Resident Temp where IncludeFlag =1;

Autonumber will generate a serial number for each of the combinations of multiple fields.

Regards,

Kiran.

[renjithpl]

getting some error..

[#4 · Accepted solution]

Simulated your case in the attachement. Hope this helps. Some syntax error in the above post

Regards,

Kiran.

[#5]

Hi ,

temp:
LOAD [Sl No.],
     [Matter ID],
     [Matter Name],
     [First Name],
     [Last Name], if(exists(MailID),'Repeating','Not repeating') as Check,
      MailID
   
FROM
[...\Book1.xls]
(biff, embedded labels, table is Sheet1$) ;

Left join
Load
MailID,

1 as Flag
resident temp where Check='Repeating';

Final:
noconcatenate
Load * resident temp ;

drop table temp;
drop field Check;

In layout you can suppress rows which have flag as null .