Filter two datatable based on two column

Help Activities
, , ,
1

HI All,
I want to filter 2 datatable based on 2 column and find unique record from dt1 into seperate datatable.
column count may be different in this datatables but 2 column are fixed and want to apply filter on them.

ex.DT1
Name-Age-City
A-20-Mumbai
B-21-Nagpur
C-22-Pune
A-23-Banglore

Dt2
Name-Age-Department
A-20-xyz
D-24-ABC

output DT3
B-21-Nagpur
C-22-Pune
A-23-Banglore
any help will be appreciated

2

For Filtering we can use Filter Datatable . For removing Duplicates we can use Remove Duplicate Rows from datatable activity.

3

A few options

  • Join DataTable - Left Join and react on the non founds
    Or
    LINQ as below

assign Activity:
dt3 =

(From d in dt1.AsEnumerable
Let c1 = d("Name").toString.Trim
Let c2 = d("Age").toString.Trim
Let arrC = new String(){c1,c2}
Let chk = dt2.asEnumerable.Any(Function (d2) {"Name","Age"}.Select(Function (x) d2(x).ToString.Trim).SequenceEqual(arrC))
Where Not chk
Select r =d).CopyToDataTable

Alternate (2 cols check without SequenceEqual Approach):

(From d In dt1.AsEnumerable
Let c1 = d("Name").toString.Trim
Let c2 = d("Age").toString.Trim
Let chk = dt2.asEnumerable.Any(Function (d2) d2("Name").ToString.Trim.Equals(c1) And d("Age").ToString.Trim.Equals(c2))
Where Not chk
Select r =d).CopyToDataTable

Handling empty rows:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

Also have a look at the Set operations Intersect, Except from LINQ

5

Thank for the reply.
it is working for without sequenceEqual but i want unique value from dt1 only.i dont want any data of dt2

6

it is not taking dt2 data. I updated the above LINQ to ommit the matchers

7

thanks i will check.

i have one doubt , how can we get last match record from datatable .
like lookup datatable activity gives first match record only. likewise i want last match record

8

we would suggest to scope 1 topic = 1 case. As others can easier find solutions for similar cases

dt2.AsEnumerable.ToList.FindLastIndex(Function (row) YOURLAMBDAPART)
will return the index or -1 when not found

[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum