How to group by multiple columns and Sum

Help Activities
1

Hi,

Sorry a read on forum same problem but can’t apply for me.
Want Group by B,C,D,E column (header dynamic)
Sum F,G,H,I (header name keep but index sometime from 5 or 6)
Thank you

image
image942×510 29.4 KB

Book1.xlsx (18.6 KB)

2

@anh.nguyen

Please use this in Assign and change column names as you need

First use a build datatable activity and then create the required group columns and sum columns and give dtoutput in datatable field


dtoutput = (From d In dtinput.AsEnumerable() Group d By k1=d(1).toString.Trim,k2=d(2).toString.Trim,k3=d(3).toString.Trim,k4=d(4).toString.Trim Into grp = Group 
Let sum1 = grp.sum(function(x) cdbl(x("Column1"))).ToString
Let sum2 = grp.sum(function(x) cdbl(x("Column2"))).ToString
Let sum3 = grp.sum(function(x) cdbl(x("Column3"))).ToString
Let sum4 = grp.sum(function(x) cdbl(x("Column4"))).ToString
Let it = grp(0)("Item NO.").ToString
Let ra = New Object(){it,k1,k2,k3,k4,sum1,sum2,sum3,sum4} Select r = Dtoutput.Rows.Add(ra)).CopyToDataTable()

Please change column names properly

Hope this helps

cheers

2 Likes
3

Hi @anh.nguyen

Please try this query:

groupedData = From row In dataTableVariable.AsEnumerable()
Group row By itemCode = row("ITEM NO.") Into itemGroup = Group
Select New With {
.ItemCode = itemCode,
.SumA = itemGroup.Sum(Function(row) If(IsNumeric(row("QUANTITY (PC/PCS)")), Convert.ToDouble(row("QUANTITY (PC/PCS)")), 0)),
.SumB = itemGroup.Sum(Function(row) If(IsNumeric(row("QUANTITY (KGS/MT)")), Convert.ToDouble(row("QUANTITY (KGS/MT)")), 0)),
.SumC = itemGroup.Sum(Function(row) If(IsNumeric(row("U PRICE")), Convert.ToDouble(row("U PRICE")), 0)),
.SumD = itemGroup.Sum(Function(row) If(IsNumeric(row("AMOUNT")), Convert.ToDouble(row("AMOUNT")), 0))
}

Hope this helps,
Best Regards.

1 Like
4

Because dynamic header so we can use index ? from B,C,D,E 1->4

5

@anh.nguyen

Exactly…as headers are dynamic i am using index…in second case headers are constant and index was dynamic so names

Cheers

6

Hi @anh.nguyen,

Try this.

(From p In DataTable1.AsEnumerable()
Group By x= New With { Key.a =p.Item(0)}
Into Grp = Group Select DataTable1.Clone.LoadDataRow (New Object() {grp(0)(0),grp(0)(1),grp(0)(2),grp(0)(3),grp(0)(4),grp.Sum(Function(h) CDbl(h(5)),grp.Sum(Function(h) CDbl(h(6)),grp.Sum(Function(h) CDbl(h(7)),grp.Sum(Function(h) CDbl(h(8))},False)).CopyToDataTable

1 Like
7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.