(Yet another) Loop through array identify unique elements / sum columns

[AmbientMike]

Hi,

I have a problem I'm trying to overcome and I've hit a brick wall with a certain aspect of it.  I have an array where one column contains many duplicates and then two other collums that I need summing IF column x is identical.

An example of this array would be...

[COMPUTERNAME1][APPLICATION1][2][4]
[COMPUTERNAME1][APPLICATION2][3][3]
[COMPUTERNAME2][APPLICATION1][7][1]
[COMPUTERNAME2][APPLICATION2][2][5]

So as an output I want to be able to basically merge the duplicate data (from column 2) whilst summing columns 3 and 4 (column 1 can be ignored)...

[APPLICATION1][9][5]
[APPLICATION2][5][8]

I don't know what the contents of column 2 are going to be.  I don't have any code to paste as an example as I haven't found any functions that will even get me started on the right path without losing data form columns 3 & 4.

Any pointers would be very welcome at this point in time.

Cheers...

[SlackerAl]

sort the input array ( _ArraySort ) on column 2. Iterate the  input array making a check to see if column 2 changes and summing columns 3 and 4 since the last time column 2 changed, when column 2 changes write column 2 and your total counts for columns 3 and 4 to an output array.

Post a running replicator of the problem for more help if needed.

 

[AmbientMike]

Many thanks, SlackerAI.  That very much pointed me in the right direction.

Here is my semi-final code...

expandcollapse popup

; Now sort the array by Application Name
_ArraySort ( $ArraySoftwareUsage, 0, 0, 0, 1)

$ArraySize2 = UBound($ArraySoftwareUsage)
$RunningTotalRuns = 0
$RunningTotalAverageTime = 0

Global $ArraySoftwareUsage2[1][4]

For $i = 0 to ($ArraySize2 - 1)

    ; Get current Application Name
    $ReadApplicationName = $ArraySoftwareUsage[$i][1]
    
    ; Read Application Name from previous line (If line No. > 0)
    If ($i > 0) Then
        $PreviousAppName = $ArraySoftwareUsage[($i -1)][1]
    Else
        $PreviousAppName = $ReadApplicationName
    EndIf
    
    $RunningTotalRuns = $RunningTotalRuns + $ArraySoftwareUsage[$i][2]
    $RunningTotalAverageTime = $RunningTotalAverageTime + $ArraySoftwareUsage[$i][3]
    
    ; When Application name changes write values to new array
    If $ReadApplicationName <> $PreviousAppName Then
        $ArrayNumber2 = $ArrayNumber2 + 1
        ReDim $ArraySoftwareUsage2[($ArrayNumber2 + 1)][4]
        ; Total up the App usage and write to new array
        $ArraySoftwareUsage2[$ArrayNumber2][0] = $Image
        $ArraySoftwareUsage2[$ArrayNumber2][1] = $ReadApplicationName
        $ArraySoftwareUsage2[$ArrayNumber2][2] = $RunningTotalRuns
        $ArraySoftwareUsage2[$ArrayNumber2][3] = $RunningTotalAverageTime
        ; Reset counting variables
        $RunningTotalRuns = 0
        $RunningTotalAverageTime = 0
    EndIf

Next

_ArrayDisplay($ArraySoftwareUsage2)

Exit

 

[Gianni]

... a way without sorting the array

#include <array.au3>

Local $aMyArray = [ _
        ['COMPUTERNAME1', 'APPLICATION1', 2, 4], _
        ['COMPUTERNAME1', 'APPLICATION2', 3, 3], _
        ['COMPUTERNAME2', 'APPLICATION1', 7, 1], _
        ['COMPUTERNAME2', 'APPLICATION2', 2, 5]]
Local $iQueryColumn = 1 ; column to query for duplicates

; find all unique elements from column1 (remove duplicates)
Local $aUniques = _ArrayUnique($aMyArray, $iQueryColumn, 0, 0, $ARRAYUNIQUE_NOCOUNT)

Local $iRows = UBound($aUniques, 1) ; number of rows for the final array (without duplicates)
Local $iCols = UBound($aMyArray, 2) ; same number of columns as the original array
Local $aResult[$iRows][$iCols] ; create the final array
Local $aAddends ; temporary array (rows with same value in column 2 to be summed)

For $i = 0 To $iRows - 1 ; for each unique value
    ; find indexes of all rows with same value in column 1 ...
    $aAddends = _ArrayFindAll($aMyArray, $aUniques[$i], 0, 0, 0, 0, $iQueryColumn)
    $iSum1 = 0
    $iSum2 = 0
    ; ... and sum values from column 2 and column 3
    For $iAddend = 0 To UBound($aAddends) - 1
        $iSum1 += $aMyArray[$aAddends[$iAddend]][2]
        $iSum2 += $aMyArray[$aAddends[$iAddend]][3]
    Next
    ; copy data from one of the duplicate rows of the original array
    For $x = 0 To $iCols - 1
        $aResult[$i][$x] = $aMyArray[$aAddends[0]][$x]
    Next
    ; and store sums to column 2 and 3
    $aResult[$i][2] = $iSum1
    $aResult[$i][3] = $iSum2
Next ; repeat for each of the unique values
_ArrayDisplay($aResult)

 

found a bug and fixed:

changed line 30 from
$aResult[$i][$x] = $aMyArray[$aAddends[  $i  ]][$x]
to
$aResult[$i][$x] = $aMyArray[$aAddends[  0  ]][$x]

Edited February 15, 2018 by Chimp
debugged code