Finding missing values in two datatable

Question

0.00/5 (No votes)

See more:

Hi
I have two data tables CurrentPayrollTable and PreviousPayrollTable. I need to compare these two tables to find out the employee number which exits in CurrentPayrollTable table but not exist in PreviousPayrollTable and employee number which exists in PreviousPayrollTable but not in CurrentPayrollTable. I tried to use below query but getting error.

Any help/pointer in this issue will be highly appreciated.

Thanks
Gaurav

What I have tried:

VB.NET

(From rowSheet1 In CurrentPayrollTable.AsEnumerable()
                    Where    (From rowSheet2 In PreviousPayrollTable.AsEnumerable()
                              Where !CInt(rowSheet1("Employee Number").ToString).Contains(CInt(rowSheet2("Employee Number").ToString))
                              Select rowSheet2).Count>0
Select rowSheet1).UNION(
(From rowSheet2 In PreviousPayrollTable.AsEnumerable()
Where    (From rowSheet1 In CurrentPayrollTable.AsEnumerable()
Where  	!CInt(rowSheet1("Employee Number").ToString).Contains(CInt(rowSheet2("Employee Number").ToString))                             						   
                               Select rowSheet1).Count>0
                    Select rowSheet2)).CopyToDatatable()

Posted 7-Jun-18 6:35am

Member 13863260

Updated 7-Jun-18 7:55am

Maciej Los

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-06-07T07:04:00

Solution 1

Try something like this:

VB.NET

Dim currentEmployees As IEnumerable(Of Integer) = CurrentPayrollTable.AsEnumerable().Select(Function (row) row.Field(Of Integer)("Employee Number"))
Dim previousEmployees As IEnumerable(Of Integer) = PreviousPayrollTable.AsEnumerable().Select(Function (row) row.Field(Of Integer)("Employee Number"))

Dim currentEmployeesNotInPrevious As IEnumerable(Of Integer) = currentEmployees.Except(previousEmployees)
Dim previousEmployeesNotInCurrent As IEnumerable(Of Integer) = previousEmployees.Except(currentEmployees)

' To get the employees who are not in both tables:
Dim employeesMissing As New HashSet(Of Integer)(currentEmployees)
employeesMissing.SymmetricExceptWith(previousEmployees)
' employeesMissing now contains employees who are in one table but not the other.

Posted 7-Jun-18 7:04am

Richard Deeming

Comments

Member 13863260 7-Jun-18 13:41pm

Thank you so much for the quick update but how i can get the result in datatable format ?

Thanks Again!
Gaurav

Richard Deeming 7-Jun-18 13:46pm

For all the employees in one table but not the other?

Dim currentRows As IEnumerable(Of DataRow) = CurrentPayrollTable.AsEnumerable().Where(Function (row) employeesMissing.Contains(row.Field(Of Integer)("Employee Number")))
Dim previousRows As IEnumerable(Of DataRow) = PreviousPayrollTable.AsEnumerable().Where(Function (row) employeesMissing.Contains(row.Field(Of Integer)("Employee Number")))

Dim result As DataTable = currentRows.Union(previousRows).CopyToDataTable()

Member 13863260 7-Jun-18 15:35pm

Thank you so much again, but when i am using below, i am getting employeesMissing no defined error.

Dim currentRows As IEnumerable(Of DataRow) = CurrentPayrollTable.AsEnumerable().Where(Function (row) employeesMissing.Contains(row.Field(Of Integer)("Employee Number")))
Dim previousRows As IEnumerable(Of DataRow) = PreviousPayrollTable.AsEnumerable().Where(Function (row) employeesMissing.Contains(row.Field(Of Integer)("Employee Number")))

Dim result As DataTable = currentRows.Union(previousRows).CopyToDataTable()

Also i tried to use below query, it is working fine but it is not comparing based on employee number only so its pulling employee number which are present in both file but with difference in other column( each table has employee number and amount column so i only want to if employee number is not present atall and it should not pull if employee number is present with diff amoutn in both table).

(From rowSheet1 In CurrentPayrollTable.Select
Select rowSheet1).Except(
(From rowSheet2 In PreviousPayrollTable.Select() Select rowSheet2)).CopyToDataTable()

Thanks a ton
Gaurav

Richard Deeming 7-Jun-18 16:34pm

Dim currentEmployees As IEnumerable(Of Integer) = CurrentPayrollTable.AsEnumerable().Select(Function (row) row.Field(Of Integer)("Employee Number"))
Dim previousEmployees As IEnumerable(Of Integer) = PreviousPayrollTable.AsEnumerable().Select(Function (row) row.Field(Of Integer)("Employee Number"))

Dim employeesMissing As New HashSet(Of Integer)(currentEmployees)
employeesMissing.SymmetricExceptWith(previousEmployees)

Dim currentRows As IEnumerable(Of DataRow) = CurrentPayrollTable.AsEnumerable().Where(Function (row) employeesMissing.Contains(row.Field(Of Integer)("Employee Number")))
Dim previousRows As IEnumerable(Of DataRow) = PreviousPayrollTable.AsEnumerable().Where(Function (row) employeesMissing.Contains(row.Field(Of Integer)("Employee Number")))

Dim result As DataTable = currentRows.Union(previousRows).CopyToDataTable()