Need help on grouping in a datatable

Help Activities
, ,
1

Hi i have datatable with columns material,unlog pt,backlogs,16.04.2024,17.04.2024…the dates column is dynamic in nature . So i need to do a group by on material column and do the sum of values present in the backlogs column and dates column for those multiple rows as single row.
Eg:
Input:
image

final output
image

The dates columns count are dynamic

2
3

i does not help…i have the query also but i as i said the dates columns are dynamic how can i achieve that for eg currently my datatable has Material,Unltpoint,backlog,16.04.2024,17.04.2024,18.04.2024,19.04.2024 next time i might have only Material,Unltpoint,backlog,16.04.2024,17.04.2024,18.04.2024 or Material,Unltpoint,backlog,16.04.2024,17.04.2024

In that case how it should work
Current query which i have
(From d In dtOrderPivot.AsEnumerable
Group d By k1=d(“Material”).toString.Trim Into grp=Group
Let s = grp.Sum(Function (x) CInt(If(String.IsNullOrEmpty(x(“Backlog”).toString.Trim),“0”,x(“Backlog”).toString.Trim)))
Let s1=grp.Sum(Function (x) CInt(If(String.IsNullOrEmpty(x(“16.04.2024”).toString.Trim),“0”,x(“16.04.2024”).toString.Trim)))
Let s2=grp.Sum(Function (x) CInt(If(String.IsNullOrEmpty(x(“17.04.2024”).toString.Trim),“0”,x(“17.04.2024”).toString.Trim)))
Let s3=grp.Sum(Function (x) CInt(If(String.IsNullOrEmpty(x(“18.04.2024”).toString.Trim),“0”,x(“18.04.2024”).toString.Trim)))
Let s4=grp.Sum(Function (x) CInt(If(String.IsNullOrEmpty(x(“19.04.2024”).toString.Trim),“0”,x(“19.04.2024”).toString.Trim)))
Let ra = New Object(){k1,grp.First()(“Unl. Point”),s,s1,s2,s3,s4}
Select r=dtOrderPivot.Rows.Add(ra)).CopyToDataTable

4

we recommend to have grounded knowledge of the grouping concept

maybe you are looking to a more dynamic approach as we do see

  • hard coded cols / sums for the columns
  • redundant code parts
5

did you get my query ? and yes i know grounded knowledge on group by queries…

6

I have prepare new query also but the sum is not happening Dim sumColumns As List(Of String) = New List(Of String) From {“Backlog”, “16.04.2024”, “17.04.2024”, “18.04.2024”, “19.04.2024”}

Dim query = From d In dtOrderPivot.AsEnumerable()
Group d By k1 = d(“Material”).ToString().Trim() Into grp = Group
Let sums = sumColumns.Select(Function(col) grp.Sum(Function(x) If(String.IsNullOrEmpty(x(col).ToString().Trim()), 0, Convert.ToInt32(x(col)))))
Let ra = {k1, CType(grp.First()(“Unl. Point”), Object)}.Concat(sums.Cast(Of Object)()).ToArray()
Select r = dtOrderPivot.Rows.Add(ra)

Dim resultTable As DataTable = query.CopyToDataTable()

7

As far we understand, the columns that are to sum up are occurring dynamic (maybe 2 cols or 5 cols …).

With:

we can assume that the needed column structure constraints are known

Assign Activity:
dtResult = dtOrderPivot.Clone

Assign Activity:
dtResult =

(From d In dtOrderPivot.AsEnumerable
Group d By k1=d(“Material”).toString.Trim Into grp=Group
Let sci = Enumerable.Range(0, dtOrderPivot.Columns.Count).Skip(2)
Let ra1 = New Object(){k1, grp.First()("Unl. Point")}
Let ra2 = sci.Select(Function (ci)  grp.Sum( Function (g) If(int32.TryParse(g(ci).ToString().Trim(), Nothing), CInt(g(ci).ToString().Trim()),0))).Cast(Of Object)
Let ra = ra1.Concat(ra2).Cast(Of Object).ToArray()
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

should work - mini test:
grafik

And can also be adapted e.g. by externalizing the
Let sci = Enumerable.Range(0, dtOrderPivot.Columns.Count).Skip(2) part

8

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