Reconcile the multi duplicate row matching issue #data tabe

Help Studio
, , , , , ,
1

kindly let me know is there any way in Linq or other way to reconcile 2 tables. the table 1 have 2 duplicate row and another table have 3 duplicate row to reconcile and get the out as 2 matched rows and one unmatched

for an example - table 1

Name | EMP No | task
bala | 2323 | RE Project
bala | 2323 | FF Project
bala | 2323 | LM project
Guna | 2211 | ka project
Sainthi | 3221 | LL Project

example - table 2

Name | EMP No | task status
bala | 2323 | complete
bala | 2323 | pending
Guna | 2211 | complete
lashen | 333 | complete

reconcile with table 1 and the table 2 based on the Name and the EMP No after reconcile the expected output matched in table 1

Name | EMP No | task
bala | 2323 | RE Project
bala | 2323 | FF Project
Guna | 2211 | ka project

expected output unmatched in table 2
Name | EMP No | task
bala | 2323 | LM project
Sainthi | 3221 | LL Project

2

We can implement such a task with the help of a stack concept. Let us reformulate the requirement, so you can check if we had correctly understood your case

dt1 and dt2 will be compared on Name & EMP no

  • when a dt1 row is NOT found in dt2 → add to unmatched set
  • when a dt1 row is found in dt2 → add to matched set
  • when more matching dt1 rows are present as present in dt2 - set the those rows to unmatched set
3

yes , more over correct.

4

Variables:
grafik

Flow:

DataGrouping:
grafik

Groups | List(Of List(of DataRow)) - outer List: Groups, inner List: Group members =

 (From d In dt1.AsEnumerable()
Group d By k1=d("Name").toString.Trim, k2=d("EMP No").toString.Trim Into grp = Group
Select g = grp.ToList).toList

Group Processing:

grafik
grafik553×648 43 KB

dt2FilterResult =
(From d In dt2.AsEnumerable
Where d(0).toString.Trim.Equals(grp.First()(0).toString.Trim)
Where d(1).toString.Trim.Equals(grp.First()(1).toString.Trim)
Select r = d).toList

MatchingRows = 
MatchingRows.Concat(grp.Take(dt2FilterResult.Count)).toList

UnmatchingRows =
UnmatchingRows.Concat(grp.Skip(dt2FilterResult.Count)).toList

Empty Result Handling:

grafik
grafik389×796 40.8 KB

dtMatched =
MatchingRows.CopyToDataTable

dtUnmatched =
UnmatchingRows.CopyToDataTable

immediate Panel result check:
grafik

We deliberately chose a hybrid approach (LINQ, essential) so that the partial results can be traced and inspected (debugging panels). We would not be able to achieve this with invoke code blackboxing

About LINQ

About grouping

And also: