Solved

VB.NET Group and Sum Custom List(of T) LINQ

Posted on 2014-11-21
4
1,446 Views
Last Modified: 2016-02-16
Hi,

i have a class 'CustomerSales' below and two lists...

Private _customerSalesList As List(Of CustomerSales)
Private _priorYearSalesList As List(Of PriorSales)

Public Class CustomerSales
    Implements IComparable(Of CustomerSales)
    Public Sub New(ByVal customerNumber As Int32,
                   ByVal groupCode As String,
                   ByVal mtdSales As Integer,
                   ByVal mtdBudget As Integer,
                   ByVal qtdSales As Integer,
                   ByVal qtdBudget As Integer,
                   ByVal ytdSales As Integer,
                   ByVal ytdBudget As Integer)
        Me.CustomerNumber = customerNumber
        Me.GroupCode = groupCode
        Me.MtdSales = mtdSales
        Me.MtdBudget = mtdBudget
        Me.QtdSales = qtdSales
        Me.QtdBudget = qtdBudget
        Me.YtdSales = ytdSales
        Me.YtdBudget = ytdBudget
    End Sub

    Public Property CustomerNumber As Int32
    Public Property GroupCode As String
    Public Property MtdSales As Int32
    Public Property MtdBudget As Int32
    Public Property QtdSales As Int32
    Public Property QtdBudget As Int32
    Public Property YtdSales As Int32
    Public Property YtdBudget As Int32

    Public Function CompareTo(ByVal other As CustomerSales) As Integer Implements IComparable(Of CustomerSales).CompareTo
        Return -YtdSales.CompareTo(other.YtdSales)
    End Function
End Class

Open in new window


Basically I need to group by GroupCode (discarding CustomerNumber of course) and Sum the rest of the fields.  Then I need to join another list on GroupCode and add the PriorMTD/QTD/YTDSales as properties to the new object.

Public Class PriorSales
    Public Sub New(ByVal organizationalCode As String, ByVal priorMtdSales As Integer, ByVal priorQtdSales As Integer, ByVal priorYtdSales As Integer)
        Me.GroupCode = organizationalCode
        Me.PriorMtdSales = priorMtdSales
        Me.PriorQtdSales = priorQtdSales
        Me.PriorYtdSales = priorYtdSales
    End Sub

    Public Property GroupCode As String
    Public Property PriorMtdSales As Int32
    Public Property PriorQtdSales As Int32
    Public Property PriorYtdSales As Int32
End Class

Open in new window


Thanks
0
Comment
Question by:FamousMortimer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 40458125
I always forget the exact syntax for grouping in VB.NET, but I believe it should be along the lines of:

Dim query = From customerSale In customerSalesCollection
            Group customerSale By customerSale.GroupCode Into Group
            Join priorSale In priorSalesCollection On GroupCode Equals priorSale.GroupCode
            Select New With
            {
                .MtdSalesTotal = Group.Sum(Function (item) item.MtdSales),
                .MtdBudgetTotal = Group.Sum(Function (item) item.MtdBudget),
                .QtdSalesTotal = Group.Sum(Function (item) item.QtdSales),
                .QtdBudgetTotal = Group.Sum(Function (item) item.QtdBudget),
                .YtdSalesTotal = Group.Sum(Function (item) item.YtdSales),
                .YtdBudgetTotal = Group.Sum(Function (item) item.YtdBudget),
                .PriorMtdSales = priorSale.PriorMtdSales,
                .PriorQtdSales = priorSale.PriorQtdSales,
                .PriorYtdSales = priorSale.PriorYtdSales
            }

Open in new window


** edited to correct a few bugs
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40458274
Hi FamousMortimer;

See if this is what you are looking for.

Dim custSalesList = (From cl As CustomerSales In _customerSalesList
                     Group By Code = cl.GroupCode Into clGroup = Group
                     Select New PriorSales(
                         organizationalCode:=Code,
                         priorMtdSales:=clGroup.Sum(Function(mtd) mtd.MtdSales),
                         priorQtdSales:=clGroup.Sum(Function(qtd) qtd.QtdSales),
                         priorYtdSales:=clGroup.Sum(Function(ytd) ytd.YtdSales)
                     )).ToList()

''  priorYearList contains those values that have the same GroupCode in both list with the new values for them
Dim priorYearList = (From pl As PriorSales In _priorYearSalesList
                     Join code In custSalesList On pl.GroupCode Equals code.GroupCode
                     Select New PriorSales(
                         organizationalCode:=pl.GroupCode,
                         priorMtdSales:=pl.PriorMtdSales + code.PriorMtdSales,
                         priorQtdSales:=pl.PriorQtdSales + code.PriorQtdSales,
                         priorYtdSales:=pl.PriorYtdSales + code.PriorYtdSales
                    )).ToList()

Open in new window

0
 
LVL 10

Author Closing Comment

by:FamousMortimer
ID: 40458284
Brilliant sir.
0
 
LVL 10

Author Comment

by:FamousMortimer
ID: 40458291
Sorry Fernando... i didn't get a chance to see your comment until after i verified that kaufmed's worked for me and assigned the points.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question