Sum values with some same group

[isim]

Hello all members

Can you help

I have table with two columns let's say column1 and column2

in column1 stores information about articles
like

1.
1.1.
1.2.
1.2.1.
2.
2.1.
2.2.1.10

and etc
in column2
stores amount of money that has each column1
like
100
200,25

I Need to sum all column2 rows data to column1
like this
1. = 1.1 +1.2 +to 1.10 (all data from column2 which meets this criteria)
1.1. =1.1.1. +1.1.2.+to 1.1.10
2.1.1. =2.1.1.1.+2.1.1.2.+ to 2.1.1.10


How to calculate this row in vb.net trouth dataset ?/??????


Any advise

 

[jmcilhinney]

In .NET 3.5 you could use LINQ. In .NET 2.0 you're just going to have to loop through all the rows in the DataTable.

Either way, you can use String.StartsWith and String.Split to determine what group each row belongs to, e.g.

Dim c1 As String = CStr(row("Column1"))

If c1.StartsWith("1.1") AndAlso c1.Split("."c).Length = 3 Then
    'This row belongs to the 1.1.x group.
End If

 

[isim]

Thanks for your replay


I see what you meen But. I need to sum all values in reverse order and there a can not figure how to do that
Little Example

First i must sum all values to the 1.1.

after that sum to 1.

Can you Help With Full Example how to that


Thank's anyway

 

[jmcilhinney]

Can you explain why reverse order is required? A sum is a sum so whay does order matter? Even if you do need to present them in reverse order, that doesn't mean you have to calculate them in that order.

 

[isim]

Becouse let's say 1.1 has 100 before summing underlyne rows

if i sum to 1. all 1.1 + 1.2. +1.3 then it not gives me write amount

so first i must sum from max(len(column1)) to down in that case 1. amount gives true summing

That's why i decided to sum in reverse order. Else if i'm not missing something

Thank's


Sorry for Bad English (I'm Learning)

 

[jmcilhinney]

So you're saying that you want to sum all the 1.1.x's into 1.1 and all the 1.2.x's into 1.2 and then you want to sum both of those into 1, correct?

 

[isim]

Yes you see what i meen

I have tried with your advise like this

   For i As Integer = 1 To maxInt
            For j As Integer = 1 To SplitString.Length - 1
            Next
        Next

Where maxInt is max possible number in my case 2
end after split Splitstring longest string is 2.1.10.1 =4

Bu i'm stuck here i can't get it

 

[isim]

And Thank's for helping you are great man

Helping me to noob

 

[jmcilhinney]

This is untested but give it a go:

'Stores the totals by key.
Private sums As New Dictionary(Of String, Integer)

'Process the entire table.
Private Sub ProcessTable(ByVal table As DataTable)
    'Process each row.
    For Each row As DataRow In table.Rows
        Me.ProcessRow(row)
    Next

    'Display the sum for each key.
    For Each key As String In Me.sums.Keys
        MessageBox.Show(Me.sums(key).ToString(), key)
    Next
End Sub

'Process a single row.
Private Sub ProcessRow(ByVal row As DataRow)
    'Split the value in the first column on the "." characters.
    Dim c1Parts As String() = CStr(row("Column1")).Split("."c)

    'Get the sum of the numbers stored in the second column.
    Dim c2Sum As Integer = Me.GetColumn2Sum(row)

    'Process each partial key from the value in the forst column.
    'E.g. if Column1 contains "1.2.3" then the partial keys are "1", "1.2" and "1.2.3".
    For count As Integer = 1 To c1Parts.Length
        'Reconstruct the partial key.
        Dim key As String = String.Join(".", c1Parts, 0, count)

        If Me.sums.ContainsKey(key) Then
            'This key already exists so add the new value to the existing value.
            Me.sums(key) += c2Sum
        Else
            'This is a new key so set the initial value.
            Me.sums(key) = c2Sum
        End If
    Next
End Sub

'Get the sum of the numbers stored in the second column.
Private Function GetColumn2Sum(ByVal row As DataRow) As Integer
    'Split the value in the first column on the "," characters.
    Dim parts As String() = CStr(row("Column2")).Split(","c)

    'Convert the substrings to Integers.
    Dim numbers As Integer() = Array.ConvertAll(parts, AddressOf Convert.ToInt32)

    'Initialise the running total to zero.
    Dim sum As Integer = 0

    'Sum the numbers.
    For Each number As Integer In numbers
        sum += number
    Next

    Return sum
End Function

 

[isim]

Thanks for reply
Maybe you didn't understand me

column1(typof string) column2(typeof decimal)
1.

​

null
1.1.

​

null
1.1.1.

​

100,15
1.1.2. 200
.
.
.
1.2. null
1.2.1. 200,50
.
1.2.10. null
1.2.10.1 300
1.2.10.2 500

i need to sum all column2 by article of column1 result must be

column1 column2
1. (100,15+200) +200,50+(300+500)
1.1. (100,15+200)
1.1.1. 100,15
1.1.2. 200
.
1.2. 200,50+(300+500)
1.2.1 200,50
1.2.10. (300+500)
1.2.10.1. 300
1.2.10.2. 500

I think i didn't understand clearly
if you see example above column 2 rows summed by rules
like 1.=(1.1.+1.2.+...)
1.1.=(1.1.1+1.1.2+..)
first column is only article and act like rule for column2 for summing

I think your first reply is getting true
becouse i don't need reverse order becouse of summing likes this gives me same result
1.=1.1.+1.1.1.+1.1.2... +1.2.+1.2.1.+..
and
1.1.=1.1.1. +1.1.2.+.. gives same result

becouse column 2 must be summed from longest articel (column1) to up where articles belongs to (1.1.1. belongs to 1.1)

there's i described more clearly in exel


thanks

 

[isim]

Thanks jmcilhinney

I have realize your first suggest
and i did thanks to you

here is what i did little code for those who will face like this problem

 For Each row As DataRow In Dataset.Table
            If (Dataset.Table.Compute("count(Column1)", String.Format("len(Column1)<>{0} and Column1 like '{1}%' ", row("Column1").ToString.Length, row("Column1")))) > 0 Then
                row("Column2")+ = Dataset.Table.Compute("Sum(Column2)", String.Format("len(Column1)<>{0} and Column1 like '{1}%'", row("Column1").ToString.Length, row("Column1")))
            End If
        Next

Big Thanks jmcilhinney