Click here to Skip to main content
15,945,344 members
Please Sign up or sign in to vote.
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
Updated 7-Jun-18 7:55am
v3

1 solution

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.
 
Share this answer
 
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()

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900