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)
Comapring two data tables
Need to create excel(third excel) of mismatch between two excel sheet
Checking if one data from an excel matches another , then getting the crossponding value
Dropdown values
Find differences in two data tables
How to extract entire row of the matched columns in 2 excel sheets
Comparing two different Excels
Join 2 datatable and bring all data of both in another datatable and find difference
#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.


#12

Hi Aksh1yadav,

Thanks for this code.

However, I am getting this error when I use it with my datatables.

Source: Assign

Message: Specified cast is not valid.

The error is from the [Component for Two Datatable Column Match and Get The Matched Records Assign activity.

Please, can you explain why?

Thanks in advance.


#13

Hey
Because the column datatype is different in your case so change that accordingly im Field< of your datatype of columo>

Regards…!!
Aksh


#14

I used this code below to display my column datatype:

DT1.Columns(“Column1”).DataType.Name.ToString()

The result is “Object”.

When I replace Int32 to Object in the original code, it gives validation errors.

Is Object a valid column datatype?

Thanks in advance


#15

Hey !

Could you pls share the sample xaml file, want to know how to use the above code i see many ppl suggesting such code , pls let me know how to use such code instead of activities.

thanks in advance


#16

Hey @siseneg_nina

Use Field(Of String) in your case please :slight_smile:

Regards…!!
Aksh


#17

Hey @shilpapmk

it is already there in the post :slight_smile:

Let me know in case if you are still facing any problem.

Regards…!!
Aksh


#18

Hey @aksh1yadav

Thanks its fine now… i had to convert the rows of the data table to string. It worked with Field(Of String) after the conversion.

Thank you very much!


#19

Hey @siseneg_nina

Even without column type conversion Field(Of String) will work but anyway if you have done :slight_smile:

Regards…!!
Aksh


#20

Its not working when I’m taking the data from Excel. Even though there is matched data it is saying source contain no data rows. Can you please advice on this?


#21

Hey @tgopalas

Will you share your sample excel?

Regards…!!
AKsh