I have a data table with 4 columns which may or may not contain duplicate values.
A duplicate value is determined by the first column and third column, if the value in column 1 and value in column 3 are the same it is considered duplicate. If there is a duplicate I want to keep the row with the max value in the second column, and the corresponding value in the 4th column, or null if it is empty.
Here is the query I’m using,
(
From row In dtIn.AsEnumerable
Group row By a1 = row(0).tostring.trim, a2 = row(2).tostring.trim Into grp = Group
Let UOM= grp.Max(Function (y) CDbl(y(1).toString.Trim))
Let COL4= grp.Where(Function (x) x(1).tostring.trim.equals(UOM.tostring)).first
Select dtIn.rows.add(a1,UOM,a2,col4(3))
).CopyToDataTable
I’m getting an error "Sequence contains no elements. "
I tried changing the .first to .firstordefault at the end of the row begining with Col4 and now I am getting "Object reference not set to an instance of an object "
Here is a sample of some of the data rows I’m using for the test.
Could you try changing the Expression to the below and check :
(From row In dtIn.AsEnumerable
Group row By a1 = row(0).tostring.trim, a2 = row(2).tostring.trim Into grp = Group
Let UOM= grp.Max(Function (y) CDbl(y(1).toString.Trim))
Let COL4= grp.Where(Function (x) x(1).tostring.trim.equals(UOM.tostring)).FirstOrDefault
Select dtIn.rows.add(a1,UOM,a2,if(COL4 is Nothing,Nothing,COL4(3))
).CopyToDataTable
We also would ask you to Create a Clone of the Input DT and assign it to a new DT and use it for the Addition of rows in the query instead of the Input DT itself.
Thanks for the response guys, I’ve tried outputting it to a cloned data table and a few other options. I just did some testing and what i am doing, is breaking it into two separate queries, First Sort Descending
(From row In dtIN.Select
Order By CDbl(row(1)) Descending Select row).CopyToDatatable()
Then I take the first record in the group by function, since the first record in the group will now be the largest value.
(
From row In dtIN
Group row By a1 = row(0).tostring.trim, a2 = row(2).tostring.trim Into grp =Group
Select grp.first
).CopyToDataTable