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
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
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.
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