Summation in 2D Array

[Stoex]

Array ABC has 0 - 6 colums, multiple rows.

Attempting to sum a value in column 4 by changes in values in column 5 and show that sum in column 6 where the break happens.

 

Col0            Col1       Col2   Col3   Col4    Col5       Col6

Row0                                              $4.50 dog

 

Row1                                               $12.49 dog

 

Row2                                                 $100 dog     $116.99  (<< this would be the total of  dog)

 

Row3                                                $2.75 bird

 

>

>

>

>

>

RowN                                                $13.25 bird   $16.00 (<< this would be the total of bird) 

for $i = 0 to UBound(ABC, 1) -1
   $j = 5
   $m = (1 +$i)
      if ABC[$i][$j] = ABC[$m][$j]Then   ;this is where the error msg shows up
         ABC[$m][6] = ABC[$i][4] + ABC[$m] [4]
      EndIf
Next

 

 

So the totalling takes place when the element in column 5 changes.  Here is what I have, but I am getting an error about the subscript and can't figure it out.

 

Error:

Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

[UEZ]

Is the array always sorted at column 5?

Br,

UEZ

[jguinch]

With a non-sorted array, you can do something like this :

#Include <Array.au3>


Local $aValues[7][3] =  [ [ 10, "dog"], [11, "cat"], [ 2.5, "bird"], [3, "bird"], [ 12, "dog"], [7, "cat"], [32, "dog"] ]
Local $aKeys[1] = [0]


For $i =  0 To UBound($aValues) - 1
    Assign("INDEX_" & $aValues[$i][1], $i )
    If IsDeclared("ITEM_" & $aValues[$i][1]) Then
        Assign("ITEM_" & $aValues[$i][1], Eval("ITEM_" & $aValues[$i][1]) + $aValues[$i][0] )
    Else
        Redim $aKeys[ UBound($aKeys) + 1]
        $aKeys[UBound($aKeys) - 1] = $aValues[$i][1]
        $aKeys[0] += 1
        Assign("ITEM_" & $aValues[$i][1], $aValues[$i][0] )
    EndIf
Next

For $i = 1 To $aKeys[0]
    $aValues[ Eval("INDEX_" & $aKeys[$i]) ][2] = Eval("ITEM_" & $aKeys[$i])
Next

_ArrayDisplay($aValues)

[Gianni]

This statement   $m = (1 +$i)
will result in an index exceeding by 1 the last element of array at end of loop.

maybe you could try like this:

For $i = 1 To UBound(ABC, 1) - 1 ; loop start at 1
    $j = 5
        $m = ($i - 1) ; instead of  $m = (1 +$i)
    If ABC[$i][$j] = ABC[$m][$j] Then ;this is where the error msg shows up
        
        ABC[$i][6] = ABC[$i][4] + ABC[$m][4] ; instead of ABC[$m][6] = ABC[$i][4] + ABC[$m] [4]
    EndIf
Next

not tested

Edited April 30, 2014 by PincoPanco

[Stoex]

Yes the array is always sorted by  col5 in order to group the data.

I see where I went wrong as well :

for $i = 0 to UBound(ABC, 1) -1 <<<<<<<< make that a -2 and I think it works.

 

I see also that it won't sum the categories in col5, but looks like it will add two.

Basically looking to 1.) with the sorted array, sum the categories in col5 by category.

[Stoex]

jguinch

 

yours works well for unsorted.  I need to use the sorted array approach.  Is your solution expandable to a sorted array?

I'm new at this so I don't want to go down dead ends. Still learning.

[jdelaney]

sure will:

#Include <Array.au3>

Global Enum $iValues_Col0, $iValues_Col1, $iValues_Col2, $iValues_Price, $iValues_Type, $iValues_Sum, $iValues_ColUBound
Local $aValues[7][$iValues_ColUBound] =  [ ["","","",10,"dog"], [ "","","",11,"cat"], [ "","","",2.5,"bird"], [ "","","",3,"bird"], [ "","","", 12,"dog"], [ "","","",7,"cat"], [ "","","",32,"dog"] ]
_ArraySort($aValues,0,0,0,$iValues_Type)
_ArrayDisplay($aValues)
Local $aKeys[1] = [0]

For $i =  0 To UBound($aValues) - 1
    Assign("INDEX_" & $aValues[$i][$iValues_Type], $i )
    If IsDeclared("ITEM_" & $aValues[$i][$iValues_Type]) Then
        Assign("ITEM_" & $aValues[$i][$iValues_Type], Eval("ITEM_" & $aValues[$i][$iValues_Type]) + $aValues[$i][$iValues_Price] )
    Else
        Redim $aKeys[ UBound($aKeys) + 1]
        $aKeys[UBound($aKeys) - 1] = $aValues[$i][$iValues_Type]
        $aKeys[0] += 1
        Assign("ITEM_" & $aValues[$i][$iValues_Type], $aValues[$i][$iValues_Price] )
    EndIf
Next

For $i = 1 To $aKeys[0]
    $aValues[ Eval("INDEX_" & $aKeys[$i])][$iValues_Sum] = Eval("ITEM_" & $aKeys[$i])
Next

_ArrayDisplay($aValues)

output:

[0]||||2.5|bird|
[1]||||3|bird|5.5
[2]||||7|cat|
[3]||||11|cat|18
[4]||||32|dog|
[5]||||10|dog|
[6]||||12|dog|54
 

Edited April 30, 2014 by jdelaney

[Gianni]

another approach

#include <Array.au3>
Global $sum
Local $aValues[7][6] = [["", "", "", 10, "dog"],["", "", "", 11, "bird"],["", "", "", 2.5, "bird"],["", "", "", 3, "bird"],["", "", "", 12, "dog"],["", "", "", 7, "cat"],["", "", "", 32, "dog"]]
_ArraySort($aValues, 0, 0, 0, 4)
_ArrayDisplay($aValues)
Local $aKeys = _ArrayUnique($aValues, 5)
For $i = 1 To $aKeys[0]
    Local $filtered = _ArrayFindAll($aValues, $aKeys[$i], 0, 0, 0, 0, 4)
    $sum = 0
    For $x = 0 To UBound($filtered) - 1
        $sum += $aValues[$filtered[$x]][3]
    Next
    $aValues[_ArraySearch($aValues, $aKeys[$i], 0, 0, 0, 0, 0, 4)][5] = $sum
Next
_ArrayDisplay($aValues)

[Stoex]

jdelaney  

 

PincoPanco

 

Nice!  I'm trying out both.

Like following your logics in order to learn more on how to code something like this.

thanks again.