Harmageddon Posted June 7, 2018 Share Posted June 7, 2018 Hi guys, I'm just started scripting and i got into kinda a strugle where I can't figure it out by myself how to get the data I need from the array. The example of Array i have: Item ID Item Name Currency Units Client ID 1 Name1 USD 10 5656565 2 Name2 EUR 15 56461314 3 Name3 EUR 6 8465142 4 Name4 USD 10 5456421 4 Name4 USD 10 213248 5 Name5 EUR 10 5481231 5 Name5 EUR -2 121554686 From this Array I need to make new array to look like this: Item ID Item Name Currency Units 1 Name1 USD 10 2 Name 2 EUR 15 3 Name3 EUR 6 4 Name4 USD 20 5 Name5 EUR 8 I've tried to search the forums for examples to make this work, however, lack of knowledge is not helping it Maybe someone has had similar case where they needed to sum up Array. Thanks in advance. Link to comment Share on other sites More sharing options...
AutoBert Posted June 7, 2018 Share Posted June 7, 2018 Try to adapt _ArraySum2DCol from this script: expandcollapse popup#include <File.au3> #include <array.au3> Global $aFile _FileReadToArray("2dSum.txt", $aFile) Global $a2D[$aFile[0]][2] _ArrayDelete($aFile, 0) For $i = 0 To UBound($aFile) - 1 $aSplit = StringSplit($aFile[$i], " ", 2) $a2D[$i][0] = $aSplit[0] $a2D[$i][1] = $aSplit[1] Next ;Summierung $aResRow = _ArraySum2DRow($a2D) ;Result in one Row _ArrayDisplay($aResRow, "Summen") $aResCol = _ArraySum2DCol($a2D, 0, 1) _ArrayDisplay($aResCol, "Summen") Func _ArraySum2DRow(ByRef $avArray) Local $aTmp = _ArrayUnique($avArray) _ArrayDelete($aTmp, 0) _ArraySort($aTmp) Local $aRes[2][UBound($aTmp)] For $i = 0 To UBound($aTmp) - 1 $aRes[0][$i] = $aTmp[$i] Next For $i = 0 To UBound($avArray) - 1 For $j = 0 To UBound($aRes, 2) - 1 ;ConsoleWrite($avArray[$i][0] & @TAB & $avArray[$i][1] & @CRLF) If $aRes[0][$j] = $avArray[$i][0] Then $aRes[1][$j] += $avArray[$i][1] Next Next Return $aRes EndFunc ;==>_ArraySum2DRow Func _ArraySum2DCol(ByRef $avArray, $iUnique = 0, $iResult = 1) Local $aTmp = _ArrayUnique($avArray, $iUnique) _ArrayDelete($aTmp, 0) _ArraySort($aTmp) Local $aRes[UBound($aTmp, 1)][2] For $i = 0 To UBound($aTmp) - 1 $aRes[$i][$iUnique] = $aTmp[$i] For $j = 0 To UBound($avArray) - 1 If $aRes[$i][$iUnique] = $avArray[$j][$iUnique] Then ;ConsoleWrite($aRes[$i][$iUnique] & @TAB & $avArray[$j][$iUnique] & @CRLF) $aRes[$i][1] += $avArray[$j][$iResult] EndIf Next Next Return $aRes EndFunc ;==>_ArraySum2DCol Tested with 2dsum.txt Link to comment Share on other sites More sharing options...
Malkey Posted June 7, 2018 Share Posted June 7, 2018 Try this. #include <Array.au3> Local $aArray[8][5] = [ _ ["Item ID", "Item Name", "Currency", "Units", "Client ID"], _ [1, "Name1", "USD", 10, 5656565], _ [2, "Name2", "EUR", 15, 56461314], _ [3, "Name3", "EUR", 6, 8465142], _ [4, "Name4", "USD", 10, 5456421], _ [4, "Name4", "USD", 10, 213248], _ [5, "Name5", "EUR", 10, 5481231], _ [5, "Name5", "EUR", -2, 121554686]] _ArrayDisplay($aArray, "Original Array") ;Local $aArr = $aArray ;ReDim $aArr[UBound($aArr)][4] ; Remove the last "Client ID" column. ; Or Local $aArr = _ArrayExtract($aArray, -1, -1, -1, UBound($aArray, 2) - 2) _ArraySort($aArr, 0, 1) ; Use only if array is not sorted. _ArrayDisplay($aArr, "4 columns") ; Sum the 4th columns of adjacent rows, if columns 1 to 3 of the adjacent rows are equal. And, remove the duplicate row. For $i = UBound($aArr) - 1 To 2 Step -1 If $aArr[$i][0] = $aArr[$i - 1][0] And $aArr[$i][1] = $aArr[$i - 1][1] And $aArr[$i][2] = $aArr[$i - 1][2] Then $aArr[$i - 1][3] += $aArr[$i][3] _ArrayDelete($aArr, $i) EndIf Next _ArrayDisplay($aArr, "Sum Identical data") Link to comment Share on other sites More sharing options...
pixelsearch Posted June 7, 2018 Share Posted June 7, 2018 @AutoBert: I found that 9 lines from your example could be replaced by 2 only, with exactly the same result : ;~ Global $aFile ;~ _FileReadToArray("2dSum.txt", $aFile) ;~ Global $a2D[$aFile[0]][2] ;~ _ArrayDelete($aFile, 0) ;~ For $i = 0 To UBound($aFile) - 1 ;~ $aSplit = StringSplit($aFile[$i], " ", 2) ;~ $a2D[$i][0] = $aSplit[0] ;~ $a2D[$i][1] = $aSplit[1] ;~ Next Global $a2D[0][2] _FileReadToArray("2dSum.txt", $a2D, $FRTA_NOCOUNT," ") A confirmation with _ArrayDisplay($a2D) shows that the Array is the same in both cases. Hope I'm not mistaken Link to comment Share on other sites More sharing options...
Harmageddon Posted June 7, 2018 Author Share Posted June 7, 2018 35 minutes ago, Malkey said: Try this. #include <Array.au3> Local $aArray[8][5] = [ _ ["Item ID", "Item Name", "Currency", "Units", "Client ID"], _ [1, "Name1", "USD", 10, 5656565], _ [2, "Name2", "EUR", 15, 56461314], _ [3, "Name3", "EUR", 6, 8465142], _ [4, "Name4", "USD", 10, 5456421], _ [4, "Name4", "USD", 10, 213248], _ [5, "Name5", "EUR", 10, 5481231], _ [5, "Name5", "EUR", -2, 121554686]] _ArrayDisplay($aArray, "Original Array") ;Local $aArr = $aArray ;ReDim $aArr[UBound($aArr)][4] ; Remove the last "Client ID" column. ; Or Local $aArr = _ArrayExtract($aArray, -1, -1, -1, UBound($aArray, 2) - 2) _ArraySort($aArr, 0, 1) ; Use only if array is not sorted. _ArrayDisplay($aArr, "4 columns") ; Sum the 4th columns of adjacent rows, if columns 1 to 3 of the adjacent rows are equal. And, remove the duplicate row. For $i = UBound($aArr) - 1 To 2 Step -1 If $aArr[$i][0] = $aArr[$i - 1][0] And $aArr[$i][1] = $aArr[$i - 1][1] And $aArr[$i][2] = $aArr[$i - 1][2] Then $aArr[$i - 1][3] += $aArr[$i][3] _ArrayDelete($aArr, $i) EndIf Next _ArrayDisplay($aArr, "Sum Identical data") Thanks Malkey, it works almost perfect. The only thing is that when I add it to the main script with more data as an example from the sample data: 1 Name1 USD 10 1 Name1 USD 5 1 Name1 USD 15 2 Name2 EUR 1 2 Name2 EUR 2 2 Name2 EUR 3 2 Name2 EUR -2 I get the result 1 Name1 USD 10 1 Name1 USD 20 2 Name2 EUR 4 All other data past row 1 and 2 are added correctly as far as I have checked with the data I have. Link to comment Share on other sites More sharing options...
Malkey Posted June 7, 2018 Share Posted June 7, 2018 If your are not having column header titles in the array, then change For $i = UBound($aArr) - 1 To 2 Step -1 to For $i = UBound($aArr) - 1 To 1 Step -1 This will include the first row of data that used to be the excluded column headers row. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now