Array help

[Si289]

Hi all,

I am trying to loop through an array (See attached) and add columns up depending on the first two numbers in Column 2.

So for example

If the first two digits in column 2 match the previous row(s) then it adds the columns up and outputs to a file.

However, my brain can not seem to get around where to start lol.

I started off with the following code:

For $d = 1 to $cArray[0][0]
        If $Code == "" Then
            $Code = $cArray[$d][2]
        Else
            $CodeLen = StringLen($cArray[$d][2])
            If $CodeLen == 4 Then
                $Code = "0"&$cArray[$d][2]
            Else
                $Code = $cArray[$d][2]
            EndIf
            $DeptCode = StringLeft($Code, 2)

However I don't know how to go about adding the columns up of $DeptCodes that match?

Any help would by much appreciated, if I can get this working it will make my life easier lol.

cArray.txt

[PsaltyDS]

This seems to work, depending on what you mean by "adding the columns up":

expandcollapse popup

Global $cArray[20][7] = [[19,6], _
    ["","Dept - Name1",07142,6,0,244,47], _
    ["","Dept - Name2",11291,0,0,12,0], _
    ["","Dept - Name3",11406,0,0,68,62], _
    ["","Dept - Name4",11406,0,0,124,0], _
    ["","Dept - Name5",16546,0,0,4,14], _
    ["","Dept - Name6",16913,0,0,391,0], _
    ["","Dept - Name7",16917,0,0,71,0], _
    ["","Dept - Name8",17458,0,0,30,0], _
    ["","Dept - Name9",18411,0,0,52,0], _
    ["","Dept - Name10",18614,0,0,25,9], _
    ["","Dept - Name11",18614,0,0,91,0], _
    ["","Dept - Name12",18978,0,0,57,0], _
    ["","Dept - Name13",19795,0,0,41,0], _
    ["","Dept - Name14",20215,2,3,2,4], _
    ["","Dept - Name15",21028,0,0,6,0], _
    ["","Dept - Name16",21756,2,0,7,0], _
    ["","Dept - Name17",22597,0,0,2,0], _
    ["","Dept - Name18",22736,0,0,104,0], _
    ["","Dept - Name19",26470,0,0,0,3]]

Global $iTotal = 0
Global $sCode = "", $sPrevious = ""

For $d = 1 To $cArray[0][0]
    $sCode = StringLeft($cArray[$d][2], StringLen($cArray[$d][2]) - 3)
    If $sCode = $sPrevious Then
        For $c = 3 To 6
            $iTotal += $cArray[$d][$c]
        Next
        ConsoleWrite($d & ":  Matched, " & $sCode & " = " & $sPrevious & ", $iTotal = " & $iTotal & @LF)
    Else
        $iTotal = 0
        For $c = 3 To 6
            $iTotal += $cArray[$d][$c]
        Next
        ConsoleWrite($d & ":  Not matched, " & $sCode & " <> " & $sPrevious & ", $iTotal = " & $iTotal & @LF)
    EndIf
    $sPrevious = $sCode
Next

Should be easy to tweak to whatever you really wanted if that's not it.

[jchd]

You need a n+½ loop (à la Dijkstra). Does that fit your bill?

expandcollapse popup

#include <Array.au3>

Local $hdl = FileOpen(@ScriptDir & "\cArray.txt")
; read headers
Local $line = FileReadLine($hdl)
; Lets see how many columns we have per line
Local $array = StringSplit($line, ',')
Local $nbcols = $array[0]
; read (and discard) second line as we don't seem to need it
FileReadLine($hdl)
; in each row, we don't sup up the first 4 columns
Local const $NoSumCols = 4
Local $sum[$nbcols]
$sum[3] = '**'                  ; make sure we don't match on first line
Local $fields
Local $output
$line = FileReadLine($hdl)
$fields = StringSplit($line, ',', 2)
While 1
    While StringLeft($fields[3], 2) = $sum[3]
        ; same deptcode, sum up remaining columns
        For $i = $NoSumCols To $nbcols - 1
            $sum[$i] += $fields[$i]
        Next
        $line = FileReadLine($hdl)
        If @error Then ExitLoop 2
        $fields = StringSplit($line, ',', 2)
    WEnd
    If $sum[3] <> '**' Then
;~      $sum[3] &= '***'
        $output = _ArrayToString($sum, @TAB, 3)
        ConsoleWrite("Department code " & $output & @LF)    ; you may need to format differently and write to file instead
    EndIf
    Dim $sum[$nbcols]
    $sum[3] = StringLeft($fields[3], 2)
WEnd
FileClose($hdl)

N.B. not much time to put error checking here, you're on your own!

Edited April 22, 2010 by jchd

[PsaltyDS]

You need a n+½ loop (à la Dijkstra).

OK, fess up. How long have been WAITING to say that on the forum somewhere?

[jchd]

But it's true my lovely Penguin!

You may read ahead the first element with or without consuming, you have to handle the read in two separate places or drag in a ugly logic more likely to confuse user/maintainer at some point.

It's not so if you use a dedicated language specialy tailored to manage N-levels of such splits (rupture is the correct word in French, but I'm not sure for english). RPG II (for Report Program Generator) was of this type (IBM/360, 370 and the like). It was a language originally created to deal with files as decks of punched cards, which you just can't read twice. So the language (called GAP II on AS/400) offered report declaratives setup to define splits like subtotals and the like at N-levels. It was very natural to code/read with no parasitic logic glue and you could programme incredibly complex reports with real ease.

I bet that if today's programmers had to code reports of the same complexity, say 5 or 6 levels such free-running totals with overlapping conditions, with some today's all-purpose language, most of them would take "some" time to get it correct...

[PsaltyDS]

But arrays, and computer storage in general, are random access now. To "define splits like subtotals" we simply declare a variable (or as many as required) and update while processing the data in any sequence we choose.

BTW, the format of the OP's data looks like it might be the result of _ExcelReadSheetToArray(). If so, these totals might have been generated with a few judicious formulas and/or macros right in Excel.

[jchd]

The n+½ is a rumor that Dijkstra hesitated to propose such a constuct in addition to the other loops in Pascal, but finaly decided it wasn't worth it. He nonetheless categorized it with the n+½ tag in his seminal papers/books.

Most probably, but that requires Office installed. And if ever there are more than one series like this in the file, Excel piloting is going to get more ... interesting.

[Si289]

Thank you for the help guys. Was able to modify the script you gave me to make it do what I want to.

I am new to this coding, so my code is not as efficient as it should be or as tidy as it should be lol.

expandcollapse popup

#Include <Excel.au3>
#Include <Array.au3>

$var = FileOpenDialog("Please select the DEPT SMALL LARGE COUNT csv file for parsing.", "C:\WorkingDir", "Comma Seperated Values (*.csv)", 5)
If @error Then
    MsgBox(4096,"","No File(s) chosen")
    Exit
EndIf

$varArray = StringSplit( $var, "|")

If $varArray[0] = "1" Then
    ;MsgBox(0,"Executing First If","Executing the first if branch")
    $Today = @YEAR & @MON  & @MDAY

    Local $oExcel = _ExcelBookOpen($varArray[1], 0)
    $aArray = _ExcelReadSheetToArray($oExcel)

    _ExcelBookClose($oExcel, 1, 0)
    ;_ArrayDisplay($aArray, "Array using Default Parameters")


    $UserCodeFile = FileOpen("C:\PrintersUsage-Code-" & $Today &".csv", 2)
    FileWriteLine($UserCodeFile, "Name,Code,Colour A4,Colour A3,Black A4,Black A3")

    For $i = 2 to $aArray[0][0] - 2
    
        ;Work out the Colour cost PER User
        
        $ColourA4 = $aArray[$i][4] + $aArray[$i][6] + $aArray[$i][12] + $aArray[$i][14] + $aArray[$i][20] + $aArray[$i][22]
        $ColourA3 = $aArray[$i][5] + $aArray[$i][7] + $aArray[$i][13] + $aArray[$i][15] + $aArray[$i][21] + $aArray[$i][23]
        $BlackA4 = $aArray[$i][8] + $aArray[$i][16] + $aArray[$i][24]
        $BlackA3 = $aArray[$i][9] + $aArray[$i][17] + $aArray[$i][25]

        if ($ColourA3 <> "0" OR $ColourA4 <> "0" OR $BlackA3 <> "0" OR $BlackA4 <> "0") Then
            ;ConsoleWrite( "Name: "& $aArray[$i][2] & " Code: "& $aArray[$i][3] & " Colour A4: " & $ColourA4 &" Colour A3: " & $ColourA3 &" Black A4: " & $BlackA4 &" Black A3: " & $BlackA3 & @CRLF )
            FileWriteLine($UserCodeFile, $aArray[$i][2] & ","& $aArray[$i][3] & "," & $ColourA4 &"," & $ColourA3 &"," & $BlackA4 &"," & $BlackA3)

        EndIf
    Next
    
FileClose($UserCodeFile)
MsgBox(48, "Success", "User Code File has been created successfully!")

Else
    ;MsgBox(0,"Executing 2nd If","Executing the 2nd if branch")
    $Today = @YEAR & @MON  & @MDAY
    $UserCodeFile = FileOpen("C:\PrintersUsage-Code-" & $Today &".csv", 1)
    FileWriteLine($UserCodeFile, "Name,Code,Colour A4,Colour A3,Black A4,Black A3")
    
    For $b = 2 to $varArray[0]
        
        ;_ArrayDisplay($varArray)
    

        Local $oExcel = _ExcelBookOpen($varArray[1]&"\"&$varArray[$b], 0)
        $aArray = _ExcelReadSheetToArray($oExcel)
        _ExcelBookClose($oExcel, 1, 0)
        
        ;_ArrayDisplay($aArray, "Array using Default Parameters")
        
        FileWriteLine($UserCodeFile, $varArray[$b] &",,,,,,")


        For $i = 2 to $aArray[0][0] - 2
    
            ;Work out the Colour cost PER User

            $ColourA4 = $aArray[$i][4] + $aArray[$i][6] + $aArray[$i][12] + $aArray[$i][14] + $aArray[$i][20] + $aArray[$i][22]
            $ColourA3 = $aArray[$i][5] + $aArray[$i][7] + $aArray[$i][13] + $aArray[$i][15] + $aArray[$i][21] + $aArray[$i][23]
            $BlackA4 = $aArray[$i][8] + $aArray[$i][16] + $aArray[$i][24]
            $BlackA3 = $aArray[$i][9] + $aArray[$i][17] + $aArray[$i][25]
            
            If ($ColourA3 <> "0" OR $ColourA4 <> "0" OR $BlackA3 <> "0" OR $BlackA4 <> "0") Then
                ;ConsoleWrite( "Name: "& $aArray[$i][2] & " Code: "& $aArray[$i][3] & " Colour A4: " & $ColourA4 &" Colour A3: " & $ColourA3 &" Black A4: " & $BlackA4 &" Black A3: " & $BlackA3 & @CRLF )
                FileWriteLine($UserCodeFile, $aArray[$i][2] & ","& $aArray[$i][3] & "," & $ColourA4 &"," & $ColourA3 &"," & $BlackA4 &"," & $BlackA3)
            EndIf
        Next
        FileWriteLine($UserCodeFile, "-,-,-,-,-,-")
    Next
    FileClose($UserCodeFile)
MsgBox(48, "Success", "User Code File has been created successfully!")
    
    
$DepartmentCodeFile = FileOpen("C:\PrintersUsage-Dept-" & $Today &".csv", 1)
;_ArrayDisplay($varArray)
For $c = 2 to $varArray[0]
        
    Local $oExcel2 = _ExcelBookOpen($varArray[1]&"\"&$varArray[$c], 0)
    $bArray = _ExcelReadSheetToArray($oExcel2)
    _ExcelBookClose($oExcel2, 1, 0)
    ;_ArrayDisplay($bArray)
    For $i = 2 to $bArray[0][0] - 2
        $ColourA4 = $bArray[$i][4] + $bArray[$i][6] + $bArray[$i][12] + $bArray[$i][14] + $bArray[$i][20] + $bArray[$i][22]
        $ColourA3 = $bArray[$i][5] + $bArray[$i][7] + $bArray[$i][13] + $bArray[$i][15] + $bArray[$i][21] + $bArray[$i][23]
        $BlackA4 = $bArray[$i][8] + $bArray[$i][16] + $bArray[$i][24]
        $BlackA3 = $bArray[$i][9] + $bArray[$i][17] + $bArray[$i][25]
    
        If ($ColourA3 <> "0" OR $ColourA4 <> "0" OR $BlackA3 <> "0" OR $BlackA4 <> "0") Then
            ;ConsoleWrite( "Name: "& $aArray[$i][2] & " Code: "& $aArray[$i][3] & " Colour A4: " & $ColourA4 &" Colour A3: " & $ColourA3 &" Black A4: " & $BlackA4 &" Black A3: " & $BlackA3 & @CRLF )
            FileWriteLine($DepartmentCodeFile, $bArray[$i][2] & ","& $bArray[$i][3] & "," & $ColourA4 &"," & $ColourA3 &"," & $BlackA4 &"," & $BlackA3)
        EndIf
    Next
Next
FileClose($DepartmentCodeFile)

Local $oExcel3 = _ExcelBookOpen("C:\PrintersUsage-Dept-" & $Today &".csv", 0)
$cArray = _ExcelReadSheetToArray($oExcel3)
_ExcelBookClose($oExcel3, 1, 0) 

;_ArrayDisplay($cArray)
_ArraySort($cArray,0,0,0,2)
;_ArrayDisplay($cArray)
    ;Start to parse the Department code.
    ;First need to put the first line into a variable
Local $oExcel4 = _ExcelBookOpen("C:\PrintersUsage-Dept-" & $Today &".csv", 0)
_ExcelWriteSheetFromArray($oExcel4, $cArray)
_ExcelBookClose($oExcel4, 1, 0)

Local $hdl = FileOpen("\\strider\tech\Printers\PrintersUsage-Dept-"&$Today&".csv", 0)
; read headers
Local $line = FileReadLine($hdl,1)
; Lets see how many columns we have per line

Local $array = StringSplit($line, ',')
Local $nbcols = $array[0]
;_ArrayDisplay($array)
; read (and discard) second line as we don't seem to need it
;FileReadLine($hdl)
; in each row, we don't sup up the first 4 columns
Local const $NoSumCols = 2
Local $sum[$nbcols]
$sum[1] = '**'                  ; make sure we don't match on first line
;_ArrayDisplay($sum)
Local $fields
Local $output
$line = FileReadLine($hdl, 1)
$fields = StringSplit($line, ',', 2)
        $CodeLen = StringLen($fields[1])
        If $CodeLen == 4 Then
            $fields[1] = "0"&$fields[1]
        EndIf
;_ArrayDisplay($fields)
;Create the OutputFile
Local $hdl2 = FileOpen("C:\PrintersUsage-DeptTotal-"&$Today&".csv", 2)
FileWriteLine($hdl2, "Department Code,Colour A4,Colour A3,Black A4,Black A3")
While 1
    While StringLeft($fields[1], 2) = $sum[1]
        ; same deptcode, sum up remaining columns
        For $i = $NoSumCols To $nbcols - 1
            $sum[$i] += $fields[$i]
        Next
        $line = FileReadLine($hdl)
        If @error Then ExitLoop 2
        $fields = StringSplit($line, ',', 2)
        $CodeLen = StringLen($fields[1])
        If $CodeLen == 4 Then
            $fields[1] = "0"&$fields[1]
        EndIf
        
    WEnd
    If $sum[1] <> '**' Then
        
;~      $sum[3] &= '***'
        $output = _ArrayToString($sum, "," , 1)
        FileWriteLine($hdl2, $output)
        ;ConsoleWrite("Department code " & $output & @LF)    ; you may need to format differently and write to file instead
    EndIf
    Dim $sum[$nbcols]
    $sum[1] = StringLeft($fields[1], 2)
    ;_ArrayDisplay($sum)
WEnd
FileClose($hdl2)
FileClose($hdl)
FileDelete("C:\PrintersUsage-Dept-"&$Today&".csv")
MsgBox(48, "Success", "Department File has been created successfully!")

EndIf

However it works which is the main thing lol.

Edited April 23, 2010 by Si289