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

[ppr (Peter | MVP)]

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:

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

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

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:
• 

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

Visuals

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

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

Visuals

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:

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

Visuals

Option 2: The LINQ Approach:

• Ensure that an empty Datatable for the report is setup with the required DataColumns:
  • dtReport2 | DataType: DataTable:
    
• Assign Activity with the LINQ statement
  

(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

• 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:

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:

[HowTo] - First Start with LINQ (VB.Net)

LINQ is part of the .NET framework and offers capabilities for interacting with data, objects or other sources within a particular syntax. This HowTo introduces on starting with LINQ (Language-Integrated Query). Current Version of this topic is DRAFT and will be finalized / enhanced soon Introduction Typical tasks for implementing parts of an automation project are interacting with e.g. with data in a datatable or items from collections. LINQ can help to implement it into a compact and stand…

• Tutorialsteacher.com
• linqsamples.com

LINQ Details

• linqsamples.com - GroupBy
• tutorialsteacher.com - GroupBy
• tutorialsteacher.com - Let Keyword

.Net Api Reference: DataTable.DefaultView

• .Net Docu - DataTable.DefaultView

Questions

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

[GreenTea]

Hi @ppr

Nice Thanks… looking forward to updates on Alternates.

[m0e91]

Thanks for your feedback.

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

Thanks in advance