Get Min and Max Value from datatable

empid LName FName HoursAllocated Employee ID - COOIS
111160 Solis Manuel 47.768 490
111160 Solis Manuel 89.7421 491
111160 Solis Manuel 175.2036 492
111161 Alton Manuel 343.6891 493
111161 Alton Manuel 683.1001 494

Based on empid get min value from HoursAllocated and max value from Employee ID - COOIS

Output:

empid LName FName HoursAllocated Employee ID - COOIS
111160 Solis Manuel 47.768 492
111161 Alton Manuel 343.6891 494

@kiran.cpavuluri
Welcome to the forum

we can achieve this grouping the data by the empid:

Option 1: filtering on distinct empids and processing the group members

after filtering we can get the:

minVal = dtFiltered.AsEnumerable.Min(Function (x) CDbl(x("oursAllocated").toString.Trim))
maxVal=  dtFiltered.AsEnumerable.Max(Function (y) CDbl(y("Employee ID - COOIS").toString.Trim))

option: LINQ statement

prepare target datatable with build datatable, configure 3 cols: empid, min, max - dtResult

use an assign activity:
LHS: dtResult
RHS:

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("empid").toString.Trim into grp=Group
Let hro = grp.Min(Function (x) CDbl(x("HoursAllocated").toString.Trim))
Let coo= grp.Max(Function (y) CDbl(y("Employee ID - COOIS").toString.Trim)) 
Let ra = new Object(){k, hro, coo}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

also have a look here:

dtDataInput.AsEnumerable.Select(Function(s) s.ItemArray.Max.ToString).ToList

dtDataInput.AsEnumerable.Select(Function(s) s.ItemArray.Min.ToString).ToList

Hi Peter,

Thanks for helping me out.

when i am using LINQ statement in assign i am only able to get output of three column remaining two column (LName, FName) those are not coming in the output datatable.

Could you please help out with this problem

Thanks,
Kiran.

Hi,

Can you try the following expression?

dtMin = {dtFiltered.AsEnumerable.OrderBy(Function (x) CDbl(x("oursAllocated").toString.Trim)).First()}.CopyToDataTable()


dtMax = {dtFiltered.AsEnumerable.OrderBy(Function (x) CDbl(x("oursAllocated").toString.Trim)).Last()}.CopyToDataTable()

dtMin and dtMax are datatable type which has single row.

Regards,

for including also LName / FName we can do:

prepare target datatable with build datatable, configure 3 cols: empid, lname, fname, min, max - dtResult
(when similar to the input datatable as an alternate: dtResult = YourDataTableVar.Clone)

then use similar following edited LINQ:

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("empid").toString.Trim into grp=Group
Let hro = grp.Min(Function (x) CDbl(x("HoursAllocated").toString.Trim))
Let coo= grp.Max(Function (y) CDbl(y("Employee ID - COOIS").toString.Trim)) 
Let ra = new Object(){k, grp.First("LName"),grp.First("FName"), hro, coo}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable