Get the diference between two datatable

Help Activities
, , ,
1

Hey everyone,

Question I will ask is easy to understand; I have 2 datatables, lets call them “mainDT” and “otherDT”, SO I compare these 2 datatables row counts if they are same I move forwards, If they are not I stop the execution and log the message saying rows counts are not same.

lets give example of the Datatables:

MainDT
(Name,no,year)
John-12-1996
Maria-13-2012
Alex-21-1984

OtherDT
(FirstName,number,year)
Maria-13-2012
John-12-1996

IF I am comparing these two examples, I want to get extra or missing value from the MainDT, In this case “Alex is missing” is the name I need because its the missing row, but lets say:

MainDT
(Name,no,year)
John-12-1996
Maria-13-2012

OtherDT
(FirstName,number,year)
Maria-13-2012
John-12-1996
Alex-21-1984

I want to get “Alex is extra” again since its the extra row.

Thank you,

2

If the number is unique for every person then you can use Join Data Table Activity. (Use Full Join)

image
image943×375 14.7 KB

3

Hope you remember the set operation as it can be used for this:

4

Okay, I remember, after I get the nonmatched datatable how can I get a string value which equals the non matched datatables’ “name” columns’ rows.

5

may we ask you on illustrate your question on sample data, thanks

6

It seems like in either case you want OtherDT to match MainDT. You could do an inner join on both DTs (ijDT).

If rows.count match for all 3 DTs, the OtherDT = MainDT. If they do not match, you would need two similar logic blocks using “For Each Row” on both MainDT and OtherDT.

If a row is in MainDT, but not ijDT, the row is missing from OtherDT.
If a row is in OtherDT, but not ijDT, the row is extra in OtherDT.

There are probably slightly faster ways to do the above, but you need to be careful overusing things like “Count” since presumably OtherDT could have both a missing row, and an extra row, so count would match the MainDT.

7

sample.xlsx (8.6 KB)

What I want is if their row count is not equal, IF MainDT has an extra row which name is the extra row, IF maindt has less row than otherdt which name is missing in MainDT.

8

dtMain.AsEnumerable.Select(Function (x) x(“Name”).toString.Trim).Except(dtOther.AsEnumerable.Select(Function (x) x(“Name”).toString.Trim)).toArray

find the names from Main which are not present in Other

Feel free to change MainDt, OtherDt in the order too check from a turned viewpoint

But as mentioned, such checks should not be done on rows count

Main: John, Anne
Other: John, Mary

we do have same row counts, but differences in the name

9

yea I know, I have other checks in the other steps so thats why I dont need the detailed check in this step. Let me try the solution you gave, I will let you know. Thank you