How to get matching entries among two datatable's

Learn
1

Hello,
I have tried to do this in so many ways but my efforts on it were all fruitless.
I have two data tables dt1 and dt2 as seen in image below. I want to do some manipulation in dt1 with reference to Name column dt2 . I want to output dt1 into two separate new data tables as see below. i want to group all rows which also have with the column Name equal to the name in dt 2 into a new data table and group where the same name column is not equal. i only want results from dt1 .
I hope i explained it well .
Thanks a lot

image
image870Ă—510 15 KB

1 Like
2

1.Use readrange to extract DT1 and DT2 as two Datatables.
2.Use for each row to loop through DT1.
3. Use Get row item to get value of column “Name”,"AGE’,“CAR” in DT1.
4.USE filter Datatable to check if the value is in DT2.
5.Check if filterDT.rows.count>=1.
6.if yes,(in ur case 4 values) add those values to new DT.
7.else,(the 2 remaining items), add those values to new DT.

Hope this works

1 Like
3

Hi @MasterOfLogic

Use Filter DataTable Activity.
Watch this video for more reference.

Thank you

4

@MasterOfLogic
have a look here:
Find_Common_NonCommon_By1Col.xaml (12.3 KB)

5

Hi @MasterOfLogic

You just need two assign activities to achieve your output by using the LINQ query as below.

dt1Output=dt1.AsEnumerable().Where(Function(row) dt2.AsEnumerable().Any(Function(x) x(“NAME”).ToString=row(“NAME”).ToString)).CopyToDatatable;

dt2Output=dt1.AsEnumerable().Where(Function(row) NOT dt2.AsEnumerable().Any(Function(x) x(“NAME”).ToString=row(“NAME”).ToString)).CopyToDatatable;

  • The dt1Output is like normal join query where dt1 matches with the NAME column of dt2

  • The dt2Output fetches data from dt1 which are not in dt2 based on NAME column

Refer the screen shot below for the workflow
image

Output screenshot:
image

You can also use SQL queries in execute query activity to achieve the output.

SQL and LINQ are powerful hence faster and uses fewer activities giving the best performance.

Note: If by chance you get “AsEnumerable is not a member of datatable” issue then this link will help you to fix: AsEnumerable is not a member of 'System.Data.Datatable' - #2 by loginerror

Please find the attached zip file to run the XAML file.
matchind_datatable.zip (8.3 KB)

4 Likes
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.