Using linq performing group by and sum of dynamic columns

Help Activities
, , ,
1

Hi all,
Can anyone please help how to do group by and sum of dynamic columns using linq(Columns are dynamic).
Attaching sample file Sheet2 is input and Sheet3 is required outputex.xlsx (9.6 KB)
e2
e3
@ppr

2

Hi,

Hope the following helps you.

img20211115-8
img20211115-81190×1467 39.5 KB 

dt = dt.AsEnumerable.GroupBy(Function(r) r("Name ").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.key}.Concat(dt.Columns.Cast(Of DataColumn).Skip(1).Select(Function(c) g.sum(Function(r) if(Int32.TryParse(r(c).ToString,new Int32), Int32.Parse(r(c).ToString),0)).ToString)).ToArray,false)).CopyToDataTable

resultDt = dt.AsEnumerable.Select(Function(r) resultDt.Clone.LoadDataRow(r.ItemArray.Concat(keywords.Select(Function(k) dt.Columns.Cast(Of DataColumn).Where(Function(c) c.ColumnName.StartsWith(k)).Sum(Function(s) Int32.Parse(r(s).ToString)).ToString)).ToArray ,False)).CopyToDataTable

arrTotal = {"Total"}.Concat(resultDt.Columns.Cast(Of DataColumn).Skip(1).Select(Function(c) resultDt.AsEnumerable.Sum(Function(r) Int32.Parse(r(c).ToString)).ToString)).ToArray

Sample20211115-2.zip (10.2 KB)

Regards,

1 Like
3

Hi @Yoichi
Thanks for your reply…It worked well but I need to use group by function for 3 columns in my present scenario how to implement in above code…(instead of group by name I need to add two more columns for grouping)

4

Hi,

I need to use group by function for 3 columns in my present scenario how to implement in above code…

For exmaple, use Tuple.Create as the following. it depends on actual datatabele structure.

dt = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Name ").ToString,r("Key2").ToString,r("Key3").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.key.Item1,g.Key.item2,g.Key.item3}.Concat(dt.Columns.Cast(Of DataColumn).Skip(1).Select(Function(c) g.sum(Function(r) if(Int32.TryParse(r(c).ToString,new Int32), Int32.Parse(r(c).ToString),0)).ToString)).ToArray,false)).CopyToDataTable

Regards,

1 Like
5

@Yoichi Thank you so much for your help…

6

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