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

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

27 Likes

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

1 Like

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

1 Like

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

Thanks in advance

2 Likes

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

1 Like

Hey @PrankurJoshi and @Sreelatha278

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

4 Likes

hey @aksh1yadav

Can you please share the sample .xaml file

1 Like

@aksh1yadav
USing 2 excel sheets as 2 datatables…

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

1 Like

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) 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.

2 Likes

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

1 Like

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.

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

Regards…!!
Aksh

1 Like

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

1 Like

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

Hey @siseneg_nina

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

Regards…!!
Aksh

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

1 Like

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!

Hey @siseneg_nina

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

Regards…!!
Aksh

1 Like

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?

Hey @tgopalas

Will you share your sample excel?

Regards…!!
AKsh