how to compare very large arrays and ReDim based on comparisons?

[BrewManNH]

I still say a binary search would be a good way to do it.

[kor]

I still say a binary search would be a good way to do it.

It might have to be.

Kafu's code is so far beyond me I have no idea how to manipulate it.

When I fed his code another sample now with 400 rows instead of the 30 in my example I attached the code doesnt work correctly again. It is calling some rows "ignores" when it should be calling them "adds"

I also tried to do a simple arraydelete on the "ignores" to completely remove those rows since they aren't really needed and no matter what I do I keep getting incorrect number of subscript errors.

Here is another sample with 400 rows.

The expected output should have rows 0 through 393 be ignore or completely absent from the final array preferably.

Rows 394 - 395 would be moves

rows 396 - 397 would be deletes

rows 398 - 399 would be adds

ad1.txt

genesis1.txt

[Spiff59]

Does the position of a record in the file matter to you?

My run using your 398/400 line files kicks out 394, 395 and 396 as UPDates, and 397 and 398 as DELetes.

I see no user names in the AD file that do not exist in the Genesis file?

If there were one, this script would output an "ADD" record...

;#include <Array.au3>
#include <File.au3>

Global $aGenesis, $aAD

_FileReadToArray(@ScriptDir & "genesis2.txt", $aGenesis)
For $x = 1 to $aGenesis[0] ; assign $aGenesis usernames as variables with index as value
    $aTemp = StringSplit($aGenesis[$x], ",")
    Assign("__" & $aTemp[1] & "_" & $aTemp[2], $x, 1)
Next

_FileReadToArray(@ScriptDir & "ad2.txt", $aAD)
For $x = 1 to $aAD[0] ; process UPD and DEL transactions
    $aTemp = StringSplit($aAD[$x], ",")
    $sTemp = "__" & $aTemp[1] & "_" & $aTemp[2]
    If IsDeclared($sTemp) Then ; is $aAD element in $aGenesis?
        $sTemp = Eval($sTemp)
        If $aAD[$x] <> $aGenesis[$sTemp] Then ; are $aAD values different than $aGenesis?
            ConsoleWrite("UPD (" & StringRight("000" & $x, 4) & ") : " & $aGenesis[$sTemp] & @CRLF)
        EndIf
        $aGenesis[$sTemp] = "" ; clear $aGenesis entry for later "ADD" transaction loop
    Else
        ConsoleWrite("DEL (" & StringRight("000" & $x, 4) & ") : " & $aAD[$x] & @CRLF)
    EndIf
Next
For $x = 1 to $aGenesis[0] ; process ADD transactions
    If $aGenesis[$x] Then ConsoleWrite("ADD (" & StringRight("000" & $x, 4) & ") : " & $aGenesis[$x] & @CRLF)
Next

Edited August 11, 2012 by Spiff59

[kor]

Spiff, your code is great!

Your rows are a little different than mine because I forgot to count row 0 in my calculations.

But you have it right that rows 394 - 396 are updates(moves), rows 397-398 are deletes,

and the adds also show as rows 397-398 but I imagine those are row count differences between the two arrays correct?

Not concerned about that tidbit as long as the final output has the right values for first name, last name, id number, location, grade, and the action of "add", "delete", "move" which your code generates very nicely.

[Spiff59]

For UPD it displays the $aAD index number and the $aGenesis values to be copied to it.

For DEL it shows the $aAD index number to delete.

For ADD, the index number shown is the $aGenesis record to be added to the $aAD file.

I suppose you would append the new records onto the end of the $aAD file? Then possibly sort it?

I didn't know what your intentions were for insertions.

Is that what you are asking?

[kor]

Mostly I'm wondering what you're doing with your Assign statement.

The data I'm going to feed to it is already in an array. I only created comma files so I could give everyone on the forum a sample of what my data looks like in the array.

From what I can gather about your assign statement is that you seem to be worried about the first name and last name as your comparison. Really the ID number which is column [2] is what I use to compare everything against since it is the unique "username" of each person.

The first name and last name and other columns are only important later in the script for the "add" actions

Also, currently your code writes to the console.. is it just as easy to spit out all that cleaned up data as a single array?

EDIT:

Confused about this

"__" & $aTemp[1] & "_" & $aTemp[2]

wondering what the point of the underscores is?

If you already had an array and didn't need to stringsplit would you still use the Assign?

Edited August 11, 2012 by kor

[Spiff59]

Maybe I didn't pay enough attention when I scanned the thread. I assumed that the first two fields made up your unique "username" like "Aaliyah,Ci".

The Assign()/IsDeclared() trick is something I saw one of the resident geniuses here (Yashied) use to find dupes in an array.

If you load a huge array of values as variables, say a list of names where "john" creates a variable $john, you can then make the query "Is there an entry for john?" with the statement IsDeclared("john") and it is *blazingly* fast. When you create the $john variable you can also assign it a value, such as the location in the array where "john" resides, and you can then use Eval() to retrieve the value of $john. With that, you can now query "Is there an entry for john, and if so where?". It's basically a lightning fast search.

It's a good idea to tack something ugly (like "__") onto the front of your not-really-a-normal-variable name so that there's no chance of "al" or "bob" or a person named "x" conflicting with a "real" variable you're using elsewhere in your program. There's also the restriction that there are some rules applying to variable names in AutoIt, so if you had a person named "hd38^@)!(*$@).,}y{4" you wouldn't be able to use this "cheaters" technique. Variable names are also case-insensitive, so you are unable to distinguish between "Bob" and "bob".

Go ahead and give a try at changing the key from what I used (a concatenation of the first 2 fields, with a "_" delimiter) to just the third field, and at converting the ConsoleWrite() to outputting into a third $aFinal array. If you have any troubles, post what you have, and I, or some other kind user, will try to help get you back on track

Edit: I stuck the underscore between the last and first names so that there was no possibility that say a "Paula Hearn" would conflict with a name like "Paul Ahearn". Without the underscore they would both try to create the same variable named $paulahearn, instead of $paula_hearn and $paul_ahearn.

Edited August 11, 2012 by Spiff59

[kor]

learning a lot in a very short time with this thread!

Spiff, I will pick back up with the code tomorrow or Sunday.

[KaFu]

Really the ID number which is column [2] is what I use to compare everything against since it is the unique "username" of each person.

The first name and last name and other columns are only important later in the script for the "add" actions

Maybe I didn't pay enough attention when I scanned the thread. I assumed that the first two fields made up your unique "username" like "Aaliyah,Ci".

You did pay attention, his explanations were just plain crappy. My code worked under the same assumption, no wonder that the results are not what was expected...

expandcollapse popup

#include <Array.au3>

$a_Input = StringSplit(FileRead("ad.txt"), @LF)
Global $aAD[$a_Input[0]][5]
For $i = 1 To $a_Input[0]
    $a_temp = StringSplit($a_Input[$i], ",")
    For $j = 0 To 4
        $aAD[$i - 1][$j] = $a_temp[$j + 1]
    Next
Next

$a_Input = StringSplit(FileRead("genesis.txt"), @LF)
Global $aGenesis[$a_Input[0]][5]
For $i = 1 To $a_Input[0]
    $a_temp = StringSplit($a_Input[$i], ",")
    For $j = 0 To 4
        $aGenesis[$i - 1][$j] = $a_temp[$j + 1]
    Next
Next

Global $oDict_Compare = ObjCreate('Scripting.Dictionary')
$oDict_Compare.CompareMode = 1
Global $s_Value, $a_Value

Global $aFinal[UBound($aAD)][6]

For $i = 0 To UBound($aGenesis) - 1
    $oDict_Compare.Add($aGenesis[$i][2], $aGenesis[$i][0] & "|||" & $aGenesis[$i][1] & "|||" & $aGenesis[$i][3] & "|||" & $aGenesis[$i][4])
Next


For $i = 0 To UBound($aAD) - 1
    If $oDict_Compare.Exists($aAD[$i][2]) Then
        If $oDict_Compare.Item($aAD[$i][2]) <> $aAD[$i][0] & "|||" & $aAD[$i][1] & "|||" & $aAD[$i][3] & "|||" & $aAD[$i][4] Then ; move
            $a_Value = StringSplit($oDict_Compare.Item($aAD[$i][2]), "|||", 1)
            $aFinal[$i][0] = $a_Value[1]
            $aFinal[$i][1] = $a_Value[2]
            $aFinal[$i][2] = $aAD[$i][2]
            $aFinal[$i][3] = $a_Value[3]
            $aFinal[$i][4] = $a_Value[4]
            $aFinal[$i][5] = "Move"
        Else ; ignore
            $aFinal[$i][0] = $aAD[$i][0]
            $aFinal[$i][1] = $aAD[$i][1]
            $aFinal[$i][2] = $aAD[$i][2]
            $aFinal[$i][3] = $aAD[$i][3]
            $aFinal[$i][4] = $aAD[$i][4]
            $aFinal[$i][5] = "Ignore"
        EndIf
        $oDict_Compare.Remove($aAD[$i][2])
    Else ; Delete
        $aFinal[$i][0] = $aAD[$i][0]
        $aFinal[$i][1] = $aAD[$i][1]
        $aFinal[$i][2] = $aAD[$i][2]
        $aFinal[$i][3] = $aAD[$i][3]
        $aFinal[$i][4] = $aAD[$i][4]
        $aFinal[$i][5] = "Delete"
    EndIf
Next
$iCount_Old = UBound($aFinal)
ReDim $aFinal[UBound($aFinal) + $oDict_Compare.Count()][6]
$i_Enum = 0
For $i In $oDict_Compare.Keys()
    $a_Value = StringSplit($oDict_Compare.Item($i), "|||", 1)
    $aFinal[$iCount_Old + $i_Enum][0] = $a_Value[1]
    $aFinal[$iCount_Old + $i_Enum][1] = $a_Value[2]
    $aFinal[$iCount_Old + $i_Enum][2] = $i
    $aFinal[$iCount_Old + $i_Enum][3] = $a_Value[3]
    $aFinal[$iCount_Old + $i_Enum][4] = $a_Value[4]
    $aFinal[$iCount_Old + $i_Enum][5] = "Add"
    $i_Enum += 1
Next

_ArrayDisplay($aFinal)

[kor]

@Spiff, I'm having a heck of a time with your code because the Assign can't be used with an array element.

The Genesis part works fine because that WILL be in a comma file just like it already is.

However the AD array will be an actual array, not a file.

I'm trying to figure out how to either change the code to allow the AD portion of the code to be an array....

Or take the array and format it back into a string so I can use your existing code. But the _ArrayToString doesn't seem to work past a 3D array.

To see what I'm talking about I'm using this code since it will convert the file back into an array exactly how my array is already formatted.

_FileReadToArray("ad1.txt", $aAD)
_ArrayDisplay($aAD)
Global $aRaw[500][5]
For $i = 0 To UBound($aAD) - 1
    Local $aTemp = StringSplit($aAD[$i], ",", 2)
    For $n = 0 To UBound($aTemp) - 1
        $aRaw[$i][$n] = $aTemp[$n]
    Next
Next
_ArrayDelete($aRaw, 0)
_ArrayDisplay($aRaw)

Edited August 11, 2012 by kor

[Spiff59]

You can use assign with array elements...

expandcollapse popup

#include <File.au3>
Global $aRaw, $aGenesis, $aAD
_FileReadToArray(@ScriptDir & "genesis2.txt", $aRaw)
Global $aGenesis[$aRaw[0] + 1][5]
$aGenesis[0][0] = $aRaw[0]
For $x = 1 to $aRaw[0]
    $aTemp = StringSplit($aRaw[$x], ",", 2)
    For $y = 0 To 4
        $aGenesis[$x][$y] = $aTemp[$y]
    Next
    Assign("__" & $aTemp[2], $x, 1)
Next
_FileReadToArray(@ScriptDir & "ad2.txt", $aRaw)
Global $aAD[$aRaw[0] + 1][5]
$aAD[0][0] = $aRaw[0]
For $x = 1 To $aRaw[0]
    Local $aTemp = StringSplit($aRaw[$x], ",", 2)
    For $y = 0 To 4
        $aAD[$x][$y] = $aTemp[$y]
    Next
Next
For $x = 1 to $aAD[0][0] ; process UPD and DEL transactions
    $sTemp = "__" & $aAD[$x][2]
    If IsDeclared($sTemp) Then ; is $aAD element in $aGenesis?
        $sTemp = Eval($sTemp)
  For $y = 0 to 4
   If $aAD[$x][$y] <> $aGenesis[$sTemp][$y] Then ; are $aAD values different than $aGenesis?
    Build_Output_Array("UPD", $x, $sTemp)
    ExitLoop
   EndIf
  Next
        $aGenesis[$sTemp][2] = "" ; clear $aGenesis entry for later "ADD" transaction loop
    Else
  Build_Output_Array("DEL", $x)
    EndIf
Next
For $x = 1 to $aGenesis[0][0] ; process ADD transactions
    If $aGenesis[$x][2] Then Build_Output_Array("ADD", $x)
Next
Func Build_Output_Array($command, $index1, $index2 = 0)
Switch $command
  Case "ADD"
   ConsoleWrite("ADD from Gen record " & $index1 & @CRLF)
  Case "DEL"
   ConsoleWrite("DEL AD record " & $index1 & @CRLF)
  Case "UPD"
            ConsoleWrite("UPD AD record " & $index1 & " from gen record " & $index2 & @CRLF)
EndSwitch
EndFunc

[kor]

Woohoo! Final working code.

A combination of Spiff and water's code.

using Spiffs assign method of searching, with waters method of populating final array.

400 records takes 1.4 seconds

expandcollapse popup

#include <File.au3>
#include <Array.au3>

Global $aRaw, $aGenesis, $aAD

_FileReadToArray(@ScriptDir & "genesis1.txt", $aRaw)
Global $aGenesis[$aRaw[0] + 1][5]
$aGenesis[0][0] = $aRaw[0]
For $x = 1 to $aRaw[0]
    $aTemp = StringSplit($aRaw[$x], ",", 2)
    For $y = 0 To 4
        $aGenesis[$x][$y] = $aTemp[$y]
    Next
    Assign($aTemp[2], $x, 1)
Next
;_ArrayDisplay($aGenesis, "genesis")


_FileReadToArray(@ScriptDir & "ad1.txt", $aRaw)
Global $aAD[$aRaw[0] + 1][5]
$aAD[0][0] = $aRaw[0]
For $x = 1 To $aRaw[0]
    Local $aTemp = StringSplit($aRaw[$x], ",", 2)
    For $y = 0 To 4
        $aAD[$x][$y] = $aTemp[$y]
    Next
Next
;_ArrayDisplay($aAD, "ad")


Global $aFinal[UBound($aAD, 1) - 1][6]

$iFinal = 0
For $i = 1 To $aAD[0][0]
    $sTemp = $aAD[$i][2]
    If IsDeclared($sTemp) Then
        $sTemp = Eval($sTemp)
        For $n = 0 To 4
            If $aAD[$i][$n] <> $aGenesis[$sTemp][$n] Then
                $aFinal[$iFinal][0] = $aGenesis[$i][0]
                $aFinal[$iFinal][1] = $aGenesis[$i][1]
                $aFinal[$iFinal][2] = $aGenesis[$i][2]
                $aFinal[$iFinal][3] = $aGenesis[$i][3]
                $aFinal[$iFinal][4] = $aGenesis[$i][4]
                $aFinal[$iFinal][5] = "Update"
                $iFinal += 1
                ExitLoop
            EndIf
        Next
        $aGenesis[$sTemp][2] = ""
    Else
        $aFinal[$iFinal][0] = $aAD[$i][0]
        $aFinal[$iFinal][1] = $aAD[$i][1]
        $aFinal[$iFinal][2] = $aAD[$i][2]
        $aFinal[$iFinal][3] = $aAD[$i][3]
        $aFinal[$iFinal][4] = $aAD[$i][4]
        $aFinal[$iFinal][5] = "Delete"
        $iFinal += 1
    EndIf
Next
For $i = 1 to $aGenesis[0][0]
    If $aGenesis[$i][2] Then
        $aFinal[$iFinal][0] = $aGenesis[$i][0]
        $aFinal[$iFinal][1] = $aGenesis[$i][1]
        $aFinal[$iFinal][2] = $aGenesis[$i][2]
        $aFinal[$iFinal][3] = $aGenesis[$i][3]
        $aFinal[$iFinal][4] = $aGenesis[$i][4]
        $aFinal[$iFinal][5] = "Add"
        $iFinal += 1
        ;Build_Output_Array("ADD", $x)
    EndIf
Next
ReDim $aFinal[$iFinal][6]
;_ArrayDisplay($aFinal)

[kor]

@Spiffy, the code seems to fall apart if the 2 arrays are not the same size.

In these attached files I've removed all the names that start with Aa from the AD.txt, and removed all the names that start with Ab from the genesis.txt and I'm getting subscript errors.

I guess it was a fluke that the previous example documents both had the same number of rows. In production the 2 arrays may or may not be the same number of rows.

EDIT: I've narrowed down the problem to be the "Move" section.

If I comment out the following

If $aAD[$i][$n] $aGenesis[$sTemp][$n] Then
                    ;ConsoleWrite($aGenesis[$i])
                    ;$aRaw[$iRaw][0] = $aGenesis[$i][0]
                    ;$aRaw[$iRaw][1] = $aGenesis[$i][1]
                    ;$aRaw[$iRaw][2] = $aGenesis[$i][2]
                    ;$aRaw[$iRaw][3] = $aGenesis[$i][3]
                    ;$aRaw[$iRaw][4] = $aGenesis[$i][4]
                    ;$aRaw[$iRaw][5] = "Move"
                    $iRaw += 1
                    ExitLoop
                EndIf

the rest of the script will work and I will be left with 3 blank spots in my Final array where the "moves" should be populated. Not sure why I am getting array subscript errors though.

EDIT2 : fixed by changing the above code to the following

If $aAD[$i][$n] <> $aGenesis[$sTemp][$n] Then
                    $aRaw[$iRaw][0] = $aGenesis[$sTemp][0]
                    $aRaw[$iRaw][1] = $aGenesis[$sTemp][1]
                    $aRaw[$iRaw][2] = $aGenesis[$sTemp][2]
                    $aRaw[$iRaw][3] = $aGenesis[$sTemp][3]
                    $aRaw[$iRaw][4] = $aGenesis[$sTemp][4]
                    $aRaw[$iRaw][5] = "Move"
                    $iRaw += 1
                    ExitLoop
                EndIf

genesis2.txt

ad2.txt

Edited August 12, 2012 by kor

[kor]

edit

Edited August 13, 2012 by kor