Linq expression to group by and sum but keep other information

[Aqiff_Shamsul]

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)

[Anil_G (Anil Gorthi)]

@Aqiff_Shamsul

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

[Yoichi]

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,

[Aqiff_Shamsul]

Thank you, this works

[Aqiff_Shamsul]

Thank you, this works! plus super easy to understand and reapply with other columns if needed.