-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 column Payment 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:-
2.Unmatched data(data present DT1 but not in DT2). Unmatched data(data present DT2 but not in DT1).
Ref.xlsx (8.8 KB)
TransItem.xls (22.5 KB)
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()
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()
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.
You can use Join Data Table activity (3 times) to fulfill your requirements…
Maybe this topic helps as it is similar
then you have to switch to:
dt2 except commonrows
Find demo here,
hasib08_II_0.3.xaml (11.0 KB)
take care about the renamed variables, do not copy paste blindly
EDIT: 0.3 Version is returning dt2 rows from Common rows check, should be more safe at your end
In general the idea is:
find common rows
join table activity + filtering out the additional join columns
using except statement for finding the unmatched rows
dt1 Except common rows
dt2 Except common rows
@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.
@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.
Have a look on this demo XAML by debugging / breakpoint pausing
indrajit.shah.xaml (12.8 KB)
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).
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
@ppr it didn’t worked, I had send u personal message, can you please check.
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?
Please find an updated version:
indrajit.shah_V4.xaml (14.4 KB)
shifted to another approach the counts are now matching
However please have a deeper, crititcal check on it.
Can be use this query matching " date column…".
If it is not
please tell how to match date column and get the row