Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

datatable
comparison

#1

Hello Community

Use Below Code to Compare 2 Datatable Column and Get Matched and Not Matched Records

Note- Based on your datatable column datatype update datatype here with column field.

To Get Matched Records

Datatable Out_Matched_Data = In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

To Get Not Matched Records

Datatable Out_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

Component -
Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)
Component for Two Datatable Column Match and Get The Non Matched Records.xaml (5.9 KB)

Sample Workflow file to use above reusable component - datatable column match.xaml (12.3 KB)

Regards…!!
Aksh


Compare column to another column in different sheet and add different values to another sheet (TIGHT DEADLINE)
Checking if one data from an excel matches another , then getting the crossponding value
Need to create excel(third excel) of mismatch between two excel sheet
Comapring two data tables
#2

SELECT *
FROM class1 c1
JOIN class2 c2 ON c1.groupName = c2.groupName
AND c1.subgroup = c2.subgroup
WHERE
(
SELECT COUNT(DISTINCT ind)
FROM class2 c2a
WHERE c2a.groupName = c1.groupName
AND c2a.subgroup = c2a.subgroup
) = 2
AND
(
SELECT COUNT(DISTINCT subgroup)
FROM class1 c1b
WHERE c1b.groupName = c1.groupName
) =
(
SELECT COUNT(DISTINCT subgroup)
FROM class2 c2b
WHERE c2b.groupName = c2.groupName
)

Try using this SQL query to get wat u want


#3

Where exactly do the first two written codes go in the first post? I don’t know where to put my excel columns in the sample workflow.

Thanks!

-Anna


#4

What if there are more than 2 Columns to be compared, can we have something for that?

Thanks in advance


#5

@aksh1yadav
Could you please help us with answer for the question asked above


#6

Hey @prankurjoshi and @MSLReddy

Yeah we have… and i am too busy so missed things and notifications. :slight_smile:
Will share my existing sample when will be on my laptop :slight_smile:
I have a sample script already for you guys.

Regards…!!
Aksh


#7

hey @aksh1yadav

Can you please share the sample .xaml file


#8

@aksh1yadav
USing 2 excel sheets as 2 datatables…

getting error (non matched data) : Specified cast is not valid…


#9

I initially had the same problem. Error can be resolved by adjusting the type of the field extension method, in my case from in32 to double as follows:

In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function® r.Field(Of Double)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Double)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

And then it works!!! Thanks Aksh for sharing this component.


#10

@aksh1yadav, thank you so much for the logic.you made my day.


#11

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)