Get distinct rows from Datatable from multiple columns using for each activity

Help
1

Hi @UiPathCommunity,
I want to compare two datatables and get distinct values based on multiple columns. Suppose i have DT1 with following data.
Name Date Of Birth Location Status
Mike 03/01/1991 Seattle Failed
Mary 01/09/1991 Seattle Failed
Pam 10/25/1998 Seattle Failed

and second dt2 with following data
Trailer No. Creation Date Ship To Status
Mike 03/01/1991 Seattle In Process
Mary 01/09/1991 Seattle In Process
Pam 10/25/1998 Seattle In Process
Lee 07/25/1998 Seattle In Process
Rick 07/25/1998 Seattle In Process

now the first three rows in dt2 are duplicate only the status is different. I want those three rows removed and get rows from dt1 with ‘failed’ status and new rows from dt2 into dt3.

I want to achieve this using for each row activity. I have attached the excel file as well.Book1.xlsx (8.3 KB) Book2.xlsx (9.1 KB)

2

datatable name.defaultview(“columnname”,true)

3

It does not solve the problem. I want to ahieve it using for each row activity.

4

Hi @Mahesh5491,

instead of doing the for loop, you can achieve the solution by single line.

 dtNew=dt.DefaultView.ToTable(True,“Column1”,“Column2”,“Column3”,“Column4”) [Specific columns]
or
 dtNew=dt.DefaultView.ToTable(True) [All the columns]

Regards
Balamurugan.S

3 Likes
5

@Mahesh5491

You can try like below,

Change the Column Names of dt2 to as it is in dt1.

Then Use Merge DataTable Activity, and merge dt2 to dt1.

Then use the below query to get the output.

dtOutPut= (From p In DT1.AsEnumerable()
               Group By x= New With { Key.a =p.Item(“Name”),Key.b=p.Item(“Date Of Birth”),Key.c=p.Item(“Location”),Key.d=p.Item(“Status”)}
               Into GroupA= Group
               Select GroupA(0)).ToArray.CopyToDataTable

Regards,
Mahesh

2 Likes