I have a long way of group by and sum it. I need a short linq expression to make my logic works. I want to group by “Job Number” and sum the column “Amount”. However, i want to keep other values and columns. Below is the excel attached. You can refer on the second sheet
Checklist Data_1401_122711.xlsx (17.0 KB)
Please rry this…i am giving only dummy column names and only few…add the column names as you need
(From d In DtBuild.AsEnumerable() Group d By k=d("job number").toString.Trim Into grp = Group Let k1=d("Row 2").toString.Trim Let sum = grp.Sum(function(x) cdbl(x("Amount").ToString)) Let ra = New Object(){k,k1,sum} Select r = DtClone.Rows.Add(ra)).CopyToDataTable()
Dtbuild - input datatable
Dtclone - output datatable…name sure to use build datatable and give a structure to this or use dtbuild.clone if structure is same
Let k1=d("Row 2").toString.Trim
- this can be repeated for each column you need from the input dtbuild and add the same in line Let ra = New Object(){k,k1,sum}
Hope this helps
Cheers
Hi,
Another approach:
How about the following with InvokeCode activity?
dt= dt.AsEnumerable.GroupBy(Function(r) r("Job Number").ToString).Select(
Function(g)
g.First.Item("Amount")=g.Sum(Function(r) CDbl(r("Amount")))
Return g.First()
End Function
).CopyToDataTable
Sample20230117-2L.zip (14.9 KB)
Regards,
Thank you, this works
Thank you, this works! plus super easy to understand and reapply with other columns if needed.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.