Datatable reconciliation

how do i validate if two particular column values from a data table dt1 exists in another datatable dt2 and if yes, the whole datatable row has to be saved in another datatable dt3

Hi @shreyaank ,

When you mention two particular values, is it not the Whole Column values from DT1 we are trying to check in DT2 ?

If it is two particular values, then do you already know these two values before hand or Do you want it to be retrieved from DT1.

If you could maybe provide us with Sample Input Data and it’s Expected Output, we could maybe able to help faster and provide accurate suggestions to your query.

When you mention two particular values, is it not the Whole Column values from DT1 we are trying to check in DT2 ? – Yes, i am just taking 2 values from 2 different columns-- ID and Year from DT1 and checking if the same combination of ID and year exists in DT2, if yes then the entire row containing all the columns must be saved in DT3

the values have to retrieved from DT1, i dont know them before hand

@shreyaank ,

Maybe you could try with the below steps :

  1. Assuming you have read the data from the two sheets and have got the datatables, let’s say DT1 and DT2.

  2. Now, We will fetch the ID and year values from DT1 and concatenate it and get all the concatenated values as a list/array.

IdYearArray = DT1.AsEnumerable.Select(Function(x)x("ID").ToString.Trim+x("Year").ToString.Trim).Distinct.ToArray

Here, IdYearArray is of the type Array of String.

  1. Next, we filter the DT2 datatable with the fetched IdYear values from DT1 like below :
DT3 = DT2.AsEnumerable.Where(Function(x)IdYearArray.Contains(x("ID").ToString.Trim+x("Year").ToString.Trim)).CopyToDatatable

Here, DT3 is the resultant datatable (needs to be created).

Also, Check the below on handling Exceptions on direct CopyToDatatable.

Let us know if you are able to follow the approach mentioned.

Hi @shreyaank

Please refer the Xaml.
test.zip (10.8 KB)

screenshot for the Understanding


fe

Hope u will get solution

Thanks Man
VP