Compare two DT with one column and get unmatched records

Help Studio
, ,
1

Hi,
-I have two data table DT1 and DT2, where I have to compare and get the mismatched records from both the data table.
-Have to compare with columnPayment reference in Ref.xlsx(excel sheet) with contains on column Trans Item in TransItem.xls(excel sheet)
-I am attaching a sample excel sheet.

In general, I am looking for 3 output:-

  1. Matched data.
    2.Unmatched data(data present DT1 but not in DT2).
  2. Unmatched data(data present DT2 but not in DT1).

Ref.xlsx (8.8 KB)
TransItem.xls (22.5 KB)

2

@indrajit.shah

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

2 Likes
4

@shanmukh_pothamsetty
I tried the below code with contains but I want output from both the DT for unmatched records, but fail to do so.

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

5

Check this post:

2 Likes
6

image
image706Ă—65 5.03 KB

7

updated the link

1 Like
8

@shanmukh_pothamsetty
it wont help, the operation i have to use is contains and there is not exact match with data in my excel file.

Although its a nice share.

9

You can use Join Data Table activity (3 times) to fulfill your requirements…

10

@indrajit.shah
Maybe this topic helps as it is similar

In general the idea is:

  • find common rows
    • join table activity + filtering out the additional join columns
    • linq statement
  • using except statement for finding the unmatched rows
  • dt1 Except common rows
  • dt2 Except common rows
11

Thankyou @bcorrea, but i am comparing one row to another using contains operation, as there is no exact match, if you can please see the attachment in the top.

Thankyou @ppr, but i don’t know but i think in my case join table activities will work, as there is no such operation called contains in it. If you see the both excel file attached with this topic you will understand my point.

12

@indrajit.shah
Have a look on this demo XAML by debugging / breakpoint pausing
indrajit.shah.xaml (12.8 KB)

13

@ppr

Thank you for your effort, but it’s now working as expected.

  • I am giving input of 269 records and getting output 31418 records.
  • There is blank output for dtTransUnmatched(which is not correct).
1 Like
14

@indrajit.shah
PFA a second iteration
indrajit.shah_V2.xaml (16.2 KB)

contains confused the logic in Version 1. But it seems to me that there are some issues with duplicates. Can you have a check on this with small testset?

Once we do know more I hope we can finalize it with a third iteration

1 Like
15

@ppr it didn’t worked, I had send u personal message, can you please check.

16

@indrajit.shah
I will have a look on it later after my work shift is done. As mentioned above I do suggest Data analysis on the duplicates. Any outcomes from this activity?

17

@indrajit.shah
Please find an updated version:
indrajit.shah_V4.xaml (14.4 KB)

shifted to another approach the counts are now matching
grafik

However please have a deeper, crititcal check on it.

1 Like
18

Can be use this query matching " date column…".
If it is not
please tell how to match date column and get the row