Group by datatable and row count

Help Activities
, ,
1

I would like group the table like below
Coulmn1 column2
99899. Add
99899. Add
99899. Edit
89768. Delete
87687. Add

Column1. Adds. Edit. Delete
99899. 2. 1. 0
89768. 0. 0. 1
87687. 1. 0. 0

1 Like
2

@Jayaraman_Kumar,

Kindly refer this.

Just modify it to have additional column.

Thanks,
Ashok :slight_smile:

1 Like
3

It provides only one row as an output. In my case it will be many based in the column 1 and also it needs to be group by column 1.

1 Like
4

HI,

FYI, another approach

image
image809×511 21.5 KB

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Column1").ToString).Select(Function(g) dtResult.LoadDataRow(new Object() {g.Key}.Concat(dtResult.Columns.Cast(Of DataColumn).Skip(1).Select(Function(dc) CObj(g.Count(Function(r) r(1).ToString=dc.ColumnName)))).ToArray,False)).CopyToDataTable

Sample
Sample20240322-2 (2).zip (9.7 KB)

Regards,

1 Like
5

Thanks for the input. The result being displayed as
Column1. Add edit. Delete
99899. 0. 0. 0
89768. 0. 0. 0

And also this add & edit and delete columns will be standard. I mean column 2 will have only this three values

1 Like
6

Hi @Jayaraman_Kumar

=> Build Data Table
image
Output-> outputDataTable

=> Read Range Workbook
image
Output-> inputDataTable

=> Use below syntax in Assign:

outputDataTable = (From row In inputDataTable.AsEnumerable()
                   Group row By key = row("Column1") Into Group
                   Let AddCount = Group.Count(Function(r) r("Column2").ToString() = "Add")
                   Let EditCount = Group.Count(Function(r) r("Column2").ToString() = "Edit")
				   Let DeleteCount = Group.Count(Function(r) r("Column2").ToString() = "Delete")
                   Select outputDataTable.Rows.Add({key, AddCount, EditCount, DeleteCount})).CopyToDataTable()

=> Write Range Workbook outputDataTable
image

SampleProcess1.zip (35.7 KB)

Regards

2 Likes
7

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