Group by column and join the other columns

Help Activities
, ,
1

HI

I have the following table

|RFC | 1 | 2 | 3 | 4 | Total|
|E21 | 1.2 | 3.5 | | | 4.7|
|A98 | | | 2.4 | | 2.4|
|T56 | | 2.1 | | 2.7 | 4.8|
|E21 | | | | 1.3 | 1.3|

and i need group by RFC but the other columns have to be joined, the output table has to be like this

|RFC | 1 | 2 | 3 | 4 | Total|
|E21 | 1.2 | 3.5 | | 1.3 | 6|
|A98 | | | 2.4 | | 2.4|
|T56 | | 2.1 | | 2.7 | 4.8|

I have the sum of the totals, but I can’t join columns 1 to 5

This is how I’m grouping and adding the totals, but i can’t join the other column

(From row In dtInput.AsEnumerable
Group row By sc = row(“RFC”).ToString()
Into grp = Group
Let total = grp.Sum(Function (x) Convert.ToDouble(x(“Total”).ToString().Trim()))
Let result = New Object() {grp(0)(“RFC”),total}
Select dtOutput.Rows.Add(result)).CopyToDataTable

2

@jessica.romero,

Have you tried Join DataTable activity?

Thanks,
Ashok :slight_smile:

3

WE would construct the full itemarray e.g By taking each column first Not null value

UPD1 -
extended to full approach

(From d In dtData.AsEnumerable
Group d By k = d("RFC").ToString().Trim Into grp = Group
Let total = grp.Sum(Function (x) Convert.ToDouble(x("Total").ToString().Trim()))
Let arrCols = {"1","2","3","4"}
Let ram = arrCols.Select(Function (c) grp.select(Function (g) g(c)).Where(Function (v) Not String.IsnullorEmpty( v.ToString().Trim())).FirstOrDefault()).toArray
Let ra = ram.Prepend(k).Append(total).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

we would recommend the following adaptions:

  • externalize and dynamize the arrCols
  • clear the need of handling e.g more then 2 rows per group

grafik

3 Likes
4

thanks!!! this is the solution i needed

5

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