[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data.

Introduction

Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items.

Lets have a look on following data:
grafik

A possible scenario could be:

  • Create a report containing following information:
    • the region code
    • the sum of CaseCount per RegionCode
    • the list of CaseNames per RegionCode

In order to get such a report we can define:

  • Grouping Criteria: The value of the Column RegionCode
  • Aggregation for the CaseCount: Summing up CaseCount values
  • Aggregation for the CaseNames: Concat the CaseName along with a seperating “;”

Grouping the Data

Approach: Filtering on distinct Group Criteria Values

Flow:

  • Retrieval of the distinct Group Criteria Values
  • Filter the datatable based on the distinct Group Criteria Values

For getting the distinct RegionCode Values the datatable.DefaultView can help:
RegionCodes = dtData.DefaultView.ToTable(True, {"RegionCode"})

Visuals

grafik
Based on the unique RegionCodes the datatable can be filtered and the different groups are retrieved:

grafik
grafik

Approach: LINQ - Group By

For retrieving the Grouped data a LINQ can be used:

Option 1: Returning the grouped data as a List(Of List(Of Datarow))

  • outer List: Groups, inner List: group members:
  • grafik
(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Select grp.toList).toList
Visuals

grafik

Option 2: Returning the grouped data as a List(Of List(Of DataTable):
grafik

(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Select grp.CopyToDataTable).toList
Visuals

grafik

Processing the grouped Data

Option 1: Iteration of the groups

Input: DataTable.DefaultView Approach
Flow:

  • Retrieval of the distinct Group Criteria Values - RegionCode | DataTable
  • For each row: Loop over RegionCode - TypeArgument: DataTable
    • Filter the datatable based on the distinct Group Criteria Values - tmpGroupDT
    • For each row: Loop over tmpGroupDT | DataTable
      • Process the group member rows

Result:
grafik

  • the CaseCounts are summed up
  • the CaseNames are concatenated and seperated by a comma
Visuals

grafik
grafik
grafik
grafik

Option 2: The LINQ Approach:

  • Ensure that an empty Datatable for the report is setup with the required DataColumns:
    • dtReport2 | DataType: DataTable:
      grafik
  • Assign Activity with the LINQ statement
    grafik
(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Let cs = grp.Sum(Function (rc) CInt( "0" & rc("CaseCount").toString))
Let cn = String.Join(";", grp.Select(Function (rn) rn("CaseName").toString).toArray)
Let ra = New Object(){k, cs, cn}
Select dtReport2.Rows.Add(ra)).CopyToDataTable
Explanation of the LINQ and Visuals

grafik

  • Line1: Loop over dtData - d is referencing the current looped datarow
  • Line2: Group Data by the k (the RegionCode) into a group - grp is referencing the current looped group
  • Line3: Memorize the sum of CaseCount for the current looped group - referenced by cs
  • Line4: Memorize the concatenated CaseNames for the current looped group - referenced by cn
  • Line5: Construct the ItemArray which will be the report line for this for the current looped group - referenced by ra
  • Line6: Use ra for adding a datarow to dtReport2 and copy the result to the DataTable

Result:
grafik

Alternates

Descriptions will be included soon

  • the Dictionary Approach
  • Decomposed LINQ
  • Activities from the marketplace

Downloads:

Find sample Project (done with 2020.10.4)
ppr_HowTo_GroupByOverview.zip (36.8 KB)

Starter Help XAML:
ppr_HowTo_GroupBy.xaml (20.7 KB)

References:

LINQ Tutorials:

LINQ Details
linqsamples.com - GroupBy

.Net Api Reference: DataTable.DefaultView

Questions

For questions on your retrieval case open a new topic and get individual support

25 Likes
Sort datatable 3 times
Sum the values of a column when it meets a condition
Sort Data Table and Send consolidate email to the Sendder
Help with creating a Linq statement
Problem Filter Data Table not working
Subset of a data from the data table for a specific Use Case
Concatenating rows
Send Excel Repot based on data from another excel
Group-by and calculate percentage
Updating excel sheet for repeated row values
Excel read duplicate rows and compare columns data to those rows
Group by sportsvenue and get sum of each group
I need explaination of this linq query
Linq Query, Please Help!
Help adding rows to each other in data table
Get Min and Max Value from datatable
How to do group by query using Linq
Group By Data Table on Date Range
Write x for each row which's column value is twice in data table
Find Duplicates and Sum the values and update in Status Column
Delete Duplicate Excel Rows Based On One Column
Data table Aggregation for dates
How to read the excel based on upper cell
How to move values from row to row
How to group an excel data based on the name column and send each group data over email
Excel rows, create list only from integer numbers of rows
What is means tolookup in linq
Group by duplicate ID and take the highest available amount from other columns
Linq Query on Group By and Sum
Trying to Group By DataTable based upon multiple columns
Get the last row index of particular duplicate value
How to process group of records in data table once instead of individually?
If Excel Row matches append document number with comma
How to group a set of comments
Save Part From The Datatable Variable
How can I continue to lookup value from DataTable
Filter some data in excel file
Group column by name in excel
Directory of directory
Data in Excel from Rows to Colums Below
How to group datatable values based on common criteria and manipulate them as a group
Selectively retrieving data from DataTable
Index of a value in Datatable
Read all data from excel and store into list
Return latest date from a duplicate row
Group Dates-By Week
Calculate Mode from a datatable tha got different values from the same name
Calculate Mode from a datatable tha got different values from the same name
Fill data in a column based on 2 condition
How do I do the Data Massaging as following in a data table?
[HowTo] LINQ (VB.Net) Learning Catalogue
Adding Sum of Specific Data In Excel
Datatable group multiple columns by list
How to get row count for each date present in Excel
How to group by multiple columns and keep all rows
DataTable Streamlining
How to count duplicate rows in a column of the same excel sheet and write it to the new excel file
Group By and Count with several conditions
How to count unique row value & insert into the same excel sheet?
How to do sum of column(Total profit) by Region in excel file
How can I remove duplicate data in datatable with using condition in columns
QUESTION AUTOMATION EXCEL-STUDIO-HTML
Split DataTable based on two unique values
Remove Dublicate Rows based on a column
DataTable has same data but quantity column values are different, Sum the quantity column and make into single row
GroupBy on 1 Column - calculate the Average and Sums of the group members
Datatable-Manipulation
How to do summation of row values, if one of the row has same value
DataTable - Process particular member from a group
How to Restructure a Datatable to convert from Row to Row and Row Count (Group By Count Operation)?
What is the best way to select a row with that a column consists a certain value in Excel
Increase same valued data consecutively if occurs more than once
Remove duplicates in two columns and keep rest of all the column values
Split Csv file into multiple files according to month in date column
How to get the first occurrence for a word
Help on Linq query
Group By ID and merge
How to add values together from different datables
N number of Random Records per Unique Combination (Group/Key)
Pivot Table Creation12
How to split an excel file into 2 files based on a list,
How to merge duplicate rows and add values of other columns

Hi @ppr

Nice :smile: Thanks… looking forward to updates on Alternates.

Thanks for your feedback.

Can u support in this, since iam not so familiar with LINQ

Thanks in advance