Array cumulative in order descending-CodePudding

I'm trying to cumulate the values of an array, grouping by the ID of column14.

My code looks like this:

Dim chartValues = New Dictionary(Of String, Decimal)()

For Each row In DataGridView1.Rows.OfType(Of DataGridViewRow)
    Dim column14Value = row.Cells().Item("Column14").Value.ToString()
    Dim column11Value = row.Cells().Item("Column11").Value

    If (chartValues.ContainsKey(column14Value)) Then
        chartValues(column14Value) = chartValues(column14Value)   column11Value
    Else
        chartValues.Add(column14Value, column11Value)
    End If
Next

For Each chartValue In chartValues
    Dim Tot As Decimal
    Tot  = chartValue.Value
    ComboBox1.Items.Add(Tot)
Next

My problem is that the order of the values in the array is in disorder, I need to sort it descending (for a Pareto) but I dont know how to do this. (The cumulate by it self it those works) Any help is really appreciated. Thanks!

CodePudding user response：

A SWAG

    Dim chartValues As New SortedDictionary(Of String, Decimal)
    For Each row As DataGridViewRow In DataGridView1.Rows.OfType(Of DataGridViewRow)()
        Dim column14Value As String = row.Cells().Item("Column14").Value.ToString()
        Dim column11Value As Decimal = CDec(row.Cells().Item("Column11").Value)

        If (chartValues.ContainsKey(column14Value)) Then
            chartValues(column14Value)  = column11Value
        Else
            chartValues.Add(column14Value, column11Value)
        End If
    Next

    For Each chartValue As Decimal In chartValues.Values
        ComboBox1.Items.Add(chartValue)
    Next

CodePudding user response：

If you want a pareto chart, you want to group by the name, take the sum of each group, also take the cumulative sum of each group. You can do this all in LINQ, though I would caution against using data directly in a DataGridView: you should keep your data off the UI. But this solution does it according to your design, using LINQ.

Dim sums = DataGridView1.Rows.OfType(Of DataGridViewRow).
    GroupBy(Function(d As DataGridViewRow) d.Cells().Item("Column14").Value.ToString()).
    Select(Function(g) New KeyValuePair(Of String, Decimal)(g.Key, g.Sum(Function(rs) CDec(rs.Cells().Item("Column11").Value)))).
    OrderByDescending(Function(kvp) kvp.Value)
Dim pareto = sums.Select(Function(kvp, i) New ParetoData(kvp.Key, kvp.Value, sums.Take(i   1).Select(Function(kvp1) kvp1.Value).Sum()))

ComboBox1.DataSource = pareto.Select(Function(p) $"Name:{p.Key}, Sum:{p.Sum}, Cum:{p.Cumulative}").ToList()

It needs this class to hold the mentioned data.

Public Class ParetoData
    Public Sub New(key As String, sum As Decimal, cumulative As Decimal)
        Me.Key = key
        Me.Sum = sum
        Me.Cumulative = cumulative
    End Sub
    Public Property Key As String
    Public Property Sum As Decimal
    Public Property Cumulative As Decimal
End Class

To test without your data, I made my own data and class to hold it

Public Class Data
    Public Sub New(column14 As String, column11 As Decimal)
        Me.Column11 = column11
        Me.Column14 = column14
    End Sub
    Public Property Column11 As Decimal
    Public Property Column14 As String
End Class

Before running the solution code above, I populated the grid like so

Dim gridRows As New List(Of Data)()
gridRows.Add(New data("D", 4))
gridRows.Add(New data("H", 1))
gridRows.Add(New data("E", 5))
gridRows.Add(New data("F", 2))
gridRows.Add(New data("G", 5))
gridRows.Add(New data("H", 8))
gridRows.Add(New data("G", 3))
gridRows.Add(New data("A", 1))
gridRows.Add(New data("B", 3))
gridRows.Add(New data("D", 2))
gridRows.Add(New data("J", 1))
gridRows.Add(New data("H", 2))
gridRows.Add(New data("E", 7))
gridRows.Add(New data("F", 6))
gridRows.Add(New data("G", 3))
gridRows.Add(New data("A", 1))
gridRows.Add(New data("B", 1))
gridRows.Add(New data("E", 1))
gridRows.Add(New data("F", 4))
gridRows.Add(New data("G", 2))
gridRows.Add(New data("G", 6))
gridRows.Add(New data("A", 1))
gridRows.Add(New data("I", 1))
gridRows.Add(New data("C", 3))
gridRows.Add(New data("G", 3))
gridRows.Add(New data("A", 1))
gridRows.Add(New data("B", 3))
gridRows.Add(New data("D", 2))
gridRows.Add(New data("B", 1))
gridRows.Add(New data("D", 2))
gridRows.Add(New data("H", 3))
gridRows.Add(New data("E", 9))

DataGridView1.DataSource = gridRows

And putting the result in the ComboBox, it looks like this