Sign in to follow this  
Followers 0
Stoex

Summation in 2D Array

9 posts in this topic

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

Share this post


Link to post
Share on other sites



Is the array always sorted at column 5?

Br,

UEZ


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by PincoPanco

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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)

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0