Hi All,
Can anyone help me on this .
I have two Data Table and I want to compare all the column values of both Data Table and find the unmatched Column . I tried using LINQ but I am getting entire row .
I just want unmatched columns. For example if Column C and D are not matching I should get only column C and Column D and not entire rows.
- DT1Whatever Columns are not matching , I should get only those Unmatched columns not the entire row
For each row there might be something sifferent not matchign also in that case what to do?
Cheers
we need to pick that corresponding column for that row. for example in row 2 the corresponding value of column end date is not matching with with end date of other sheet , then get the end date
So you need the quote number and mismatched column name?
If more than one column name is not matching then should we comma separate all column names ?
Cheers
for example in Q30564 the payment term is Net30 and other datatable it is Net 60. Hence need to pick Total Contract Value
Can we do one to one compare of each column in both DataTable. For example Quote Number with Quote Number, Payment Term with Payment Term etc and find any mismatch get that columnnames
I used this query but getting entire rows of matched and unmatched column.
I used this query but getting entire rows along with unmatched columns
(From d In DT1.AsEnumerable
Let k1 = d(“QuoteNumber”).ToString.Trim
Let k2 = d(“StartDate”).ToString.Trim
Let k3 = d(“PaymentTerms”).ToString.Trim
Let k4 = d(“Total Contract Value”).ToString.Trim
Let k5 = d(“Primary Contact”).ToString.Trim
Let k6 = d(“Scheduling & Delivery”).ToString.Trim
Let k7 = d(“EndDate”).ToString.Trim
Where Not DT2.AsEnumerable.Any(Function(d2) d2(“QuoteNumber”).ToString.Trim.Equals(k1) AndAlso d2(“StartDate”).ToString.Trim.Equals(k2) AndAlso d2(“PaymentTerms”).ToString.Trim.Equals(k3) AndAlso d2(“Total Contract Value”).ToString.Trim.Equals(k4) AndAlso d2(“Primary Contact”).ToString.Trim.Equals(k5) AndAlso d2(“Scheduling & Delivery”).ToString.Trim.Equals(k6) AndAlso d2(“EndDate”).ToString.Trim.Equals(k7))
Select r = d).CopyToDataTable
I got the entire row of match and unmatch columns
Can you try this
dt1.AsEnumerable.Select(function(x) dt3.Clone.LoadDataRow({x("Quote Number").ToString,String.Join(",",dt2.AsEnumerable.First(function(y) y("Quote Number").ToString.Equals(x("Quote Number").ToString)).ItemArray.Where(function(y,i) Not y.ToString.Equals(x(i).ToString)))},False)).CopyToDataTable
dt3 should be two columns or string type…use build data table and build it…gives you the quote number and corresponding mismatched values
cheers
for dt3 what should be the column name I should give ?
Ok. I shall take dt3 in assign activity ?
In assign activity I should take dt3= dt1.AsEnumerable.Select(function(x) dt3.Clone.LoadDataRow({x(“Quote Number”).ToString,String.Join(“,”,dt2.AsEnumerable.First(function(y) y(“Quote Number”).ToString.Equals(x(“Quote Number”).ToString)).ItemArray.Where(function(y,i) Not y.ToString.Equals(x(i).ToString)))},False)).CopyToDataTable
Hi Anil,
I am almost near to Result.
Just one thing can I get the mismatch Column Names.
Below is the output which I got and Also is it possible to compare Null value with Column Value. For example In Scheduling&Delivery column QuoteNumber 29530 has text but other Quote doesnot have. Is it possible to compare
for no column you can add a column using add data column and compare else there is nothign to comapre so it would not
add it in same order
for column names try this
dt1.AsEnumerable.Select(function(x) dt3.Clone.LoadDataRow({x("Quote Number").ToString,String.Join(",",dt2.AsEnumerable.First(function(y) y("Quote Number").ToString.Equals(x("Quote Number").ToString)).ItemArray.Select(function(y,i) If(Not y.ToString.Equals(x(i).ToString),dt1.Columns(i).ColumnName,"")).Cast(Of String).Where(function(z) Not STring.IsNullOrEmpty(z)))},False)).CopyToDataTable
cheers
For the query of column names , I am getting below error . Its showing sequence contains no matching elements
Looks like the First condition of matching is failing…please check if there are any matching quote numbers…
Cheers
All the three Quote numbers are matching in both sheets.(Both DT1 and DT2 have matching Quote Numbers)
can you check if any of the datatable is empty…as I ran the query with sample data and output is as expected
cheers
DT1 is first datatable for Read Range.
DT2 for second Read range
dt3 is my datatable dt_results1
I did build datatable dt_results1
did you add the missing column?
Please attach sample here as for the sample with me it si working
Also remove the first row from the build datatable for result
dt1
dt2
dt3
Result
cheers
Anil_G
ashokkarale
Yoichi
Kismet_Tosun
marina.dutta
pica
VitaminJapan2
Juan_Felipe_Carvajal
SorenB
Kiran-Kishore