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

News Tutorials
, , , , ,
1

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

43 Likes
2

Hi @ppr

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

3

Thanks for your feedback.

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

Thanks in advance

4

This is perfect but what if i want to group by more than 2 columns ?