Compare 2 columns from 2 different DT

I am having 2 DT with more than 2000 rows and 20 columns.

Inputs:
DT1 - text column (Enterprise ID) Note: (Director ID) column in DT1 is empty
DT2 - text column (Director ID)

Expected output
FinalDT - All the rows from DT1 (Enterprise ID) which is not present in DT2 (Director ID).

Solutions tried:
Join Data Table Activity(Inner/ Left join) - Returning unwanted rows along with correct ones.

DT1
image

DT2
image

@ppr

Thanks in advance

Show the output you want…

I am not able to understand your expected output

Hey @Athawale_Rushikesh,

You can use inputDT1.AsEnumerable().Where(function(row) Not inputDT2.AsEnumerable().Select(function(r) r(“ColumnName”).ToString).Any(function(x) x = row(“ColumnName”).ToString)).CopyToDataTable()

Thanks,
Sanjit

with the hint of

we do feel that that we cannot take directly the dt1 rows and maybe some transfers from dt2 to dt1 rows are to do.

If this is not the case give a try at:

dtResult =

(From d in dt1.asEnumerable
Where Not dt2.AsEnumerable.Any(Function (x) x("EnterpriseID").ToString.Trim.Equals(d("EnterpriseID").ToString.Trim)
Select r = d).CopyToDataTable

Here is the sample WB with Input and Output DT format
Sample Data.xlsx (20.2 KB)

Thanks
Rushi

in alignement with your later shared sample data we do see that also a deduplication is needed

give a try at:
dtResult =

(From d in dt1.DefaultView.ToTable(True).AsEnumerable
Where Not dt2.AsEnumerable.Any(Function (x) x("EnterpriseID").ToString.Trim.Equals(d("EnterpriseID").ToString.Trim))
Select r = d).CopyToDataTable

Hi @ppr

I am getting error in below query


I am trying without deduplication, duplication is not an issue I can take care of that in next workflow.

Thanks,
Rushi

just corrected with a ) at the end of trim part. Try again

below is the result I got.
Orange marked is the unwanted one and Yellow ones should have been included in result.

image

Hey @ppr I got the result correct

Below should have been the query. Well thanks for direction :slight_smile:

(From d in dt1.DefaultView.ToTable(True).AsEnumerable
Where Not dt2.AsEnumerable.Any(Function (x) x(“DirectorID”).ToString.Trim.Equals(d(“EnterpriseID”).ToString.Trim))
Select r = d).CopyToDataTable

Thanks a lot
Rushi

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