Get nonmmatching rows from Input dt table on matching with commondtTable

Help Activities
, ,
1

Hello,

I have designed linq query to get non matching rows based on column value match. But the result is returning inaccurate value with all the rows from input table instead of getting non matching rows.
in_dt_InputExcelRecords.AsEnumerable().Where(Function(row) not in_dt_CommonTableRecords.AsEnumerable().Where(function(r) r(“transactionReference”).ToString.Trim.Equals(row(“What is your Employee ID? ¿Cual es tu ID de empleado?”).ToString.Trim) andalso r(“customData3”).ToString.Contains(row(“Please provide the date of your absence (MM/DD/YR):”).ToString) andalso r(“customData2”).ToString.Equals(row(“What is your reason for absence”).ToString)).Any).ToArray

Is anything incorrect in this query

2

try this

in_dt_InputExcelRecords.AsEnumerable().
Where(Function(row)
not in_dt_CommonTableRecords.AsEnumerable().
Where(Function(r)
r(“transactionReference”).ToString.Trim().Equals(row(“What is your Employee ID? ¿Cual es tu ID de empleado?”).ToString.Trim()) AndAlso
r(“customData3”).ToString.Contains(row(“Please provide the date of your absence (MM/DD/YR):”).ToString.Trim()) AndAlso
r(“customData2”).ToString.Equals(row(“What is your reason for absence”).ToString.Trim())
).Any()
).ToArray()

3

still same number of records are coming

4

Hi @RPA24

Have you tried using And instead of AndAlso logic, also check if there is any space available is your strings if so use TRIM function.

5

Upon using same set of result is retrieved. If we have any alternative query to get non matching rows in Input table with comparison on commontablequery for provided column names above is welcomed

6

Please Briefly Explain.

7

We have 2 datatables -

  1. in_dt_InputExcelRecords - containing input excel records. Columns used (Employee ID, Reason of Absence, Date of Absence)
  2. in_dt_CommonTableRecords - containing records from database .Columns used (Employee ID, Reason of Absence, Date of Absence)

We want to get records from in_dt_InputExcelReason which are not present in in_dt_CommonTablerecords based on column values match from both the datatables.

I hope this problem statement is enough

8

hey @RPA24
try out this

Dim unmatchedRecords = If(in_dt_InputExcelRecords.AsEnumerable().
Where(Function(row) Not in_dt_CommonTableRecords.AsEnumerable().
Any(Function(commonRow)
row(“Employee ID”).ToString().Trim() = commonRow(“Employee ID”).ToString().Trim() AndAlso
row(“Reason of Absence”).ToString().Trim() = commonRow(“Reason of Absence”).ToString().Trim() AndAlso
row(“Date of Absence”).ToString().Trim() = commonRow(“Date of Absence”).ToString().Trim()
)
).Any(),
in_dt_InputExcelRecords.AsEnumerable().
Where(Function(row) Not in_dt_CommonTableRecords.AsEnumerable().
Any(Function(commonRow)
row(“Employee ID”).ToString().Trim() = commonRow(“Employee ID”).ToString().Trim() AndAlso
row(“Reason of Absence”).ToString().Trim() = commonRow(“Reason of Absence”).ToString().Trim() AndAlso
row(“Date of Absence”).ToString().Trim() = commonRow(“Date of Absence”).ToString().Trim()
)
).CopyToDataTable(),
Nothing
)

9

Hi @RPA24 I have little different approach for this as you have data in datatable. Are you using excel & these are 2 sheets? If yes you can use excel as database using oledb connection. Simply run a left join query on it & you will get result in datatable.

SELECT A.* 
FROM [InputExcelRecords$] AS A
LEFT JOIN [CommonTableRecords$] AS B
ON A.[Employee ID] = B.[Employee ID]
   AND A.[Reason of Absence] = B.[Reason of Absence]
   AND A.[Date of Absence] = B.[Date of Absence]
WHERE B.[Employee ID] IS NULL;

Hope this helps :slight_smile:

10

This gives same output as of earlier linq

11

We are using excel but only for reading the data and storing in datatable. DO we have any other alternative?