Array Comparison - (Moved)

[mknope]

Hello, I have done a few programs to automate processes using AutoIt but I am a greenhorn when trying to do a comparison between two arrays my code is writing out. 

I am trying to go out to a webpage that contains a listing of Solution Changes in a table (distribution A) and compare that array to the listing of Solution changes from distribution B. 

Ultimately, I want to know which solution change numbers (ex 1-5091919681) exist in A but not B, and ones that exist in B but not A. I am not entirely sure where to start and thus the reason for posting this. Any help or direction would be appreciated. 

Func distribution_search_a()
$oForm = _IEFormGetObjByName($oIE, "frmMain") 
$oLogin = _IEFormElementGetObjByName($oForm, "pkg") 
$oSubmit = _IEGetObjByName($oIE, "btnSubmit")
_IEFormElementSetValue($oLogin, GUICtrlRead($Package_A_Input))
_IEAction($oSubmit, "click")
sleep(2000)
_IELinkClickByText($oIE, "Solution Changes")


Global $aTable=_IEGetObjById($oIE,"CRList")
Global $aTableData=_IETableWriteToArray($aTable)
;_ArrayDisplay($aTableData)
Endfunc

Func distribution_search_b()
$oForm = _IEFormGetObjByName($oIE, "frmMain") 
$oLogin = _IEFormElementGetObjByName($oForm, "pkg")
$oSubmit = _IEGetObjByName($oIE, "btnSubmit")
_IEFormElementSetValue($oLogin, GUICtrlRead($Package_B_Input)) 
_IEAction($oSubmit, "click")
sleep(2000)
_IELinkClickByText($oIE, "Solution Changes")



Global $bTable=_IEGetObjById($oIE,"CRList")
Global $bTableData=_IETableWriteToArray($bTable)
;_ArrayDisplay($bTableData)
Endfunc

 

[Melba23]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[water]

Welcome to AutoIt and the forum!

Maybe this thread gets you started:

 

[water]

Or this one:

[mknope]

Thank you for those suggestions. When I performed the array compare using _ArrayDelete, the final array displayed has nothing in it.

This is how each array looks before the comparison:

Array A:

 

Array B: 

expandcollapse popup

Func distribution_search_b()
$oForm = _IEFormGetObjByName($oIE, "frmMain") ;Get the form "Name" (view source on the page, look for "<Form" and look for the "name=" value for the name value).
$oLogin = _IEFormElementGetObjByName($oForm, "pkg") ;The "email_login" is the name of the inputbox for the input (also in the view source).
$oSubmit = _IEGetObjByName($oIE, "btnSubmit")
_IEFormElementSetValue($oLogin, GUICtrlRead($Package_B_Input)) ;Set the login to equal this value.
_IEAction($oSubmit, "click")
sleep(2000)
_IELinkClickByText($oIE, "Solution Changes")


Global $bTable=_IEGetObjById($oIE,"CRList")
Global $bTableData=_IETableWriteToArray($bTable, True)
;_ArrayDisplay($bTableData)
Endfunc

Func distribution_search_b()
$oForm = _IEFormGetObjByName($oIE, "frmMain") ;Get the form "Name" (view source on the page, look for "<Form" and look for the "name=" value for the name value).
$oLogin = _IEFormElementGetObjByName($oForm, "pkg") ;The "email_login" is the name of the inputbox for the input (also in the view source).
$oSubmit = _IEGetObjByName($oIE, "btnSubmit")
_IEFormElementSetValue($oLogin, GUICtrlRead($Package_B_Input)) ;Set the login to equal this value.
_IEAction($oSubmit, "click")
sleep(2000)
_IELinkClickByText($oIE, "Solution Changes")


Global $bTable=_IEGetObjById($oIE,"CRList")
Global $bTableData=_IETableWriteToArray($bTable, True)
;_ArrayDisplay($bTableData)
Endfunc

Func Array_Compare()
For $i = UBound($aTableData) - 1 To 0 step -1
    For $j = UBound($bTableData) - 1 to 0 step - 1
        If  $aTableData[$i][1] = $bTableData[$j][1] Then
           _ArrayDelete($aTableData , $i)
           exitloop
        EndIf
    Next


Next
_ArrayDisplay($aTableData)

EndFunc

 

[iamtheky]

can you write those arrays to files and attach them? Or just copying them in as text rather than images would be of equal help.

Edited July 12, 2018 by iamtheky

[Gianni]

if you are interested to compare only values from the column 0, then the post you can find at this link can be handy:
for example pass to the function 2 arrays containing values from column 0 of A and B  and a 2D array will be returned where values of column 0 are those present only in array A, those in column 1 are only present in array B, and values in column 2 are present in both arrays:

expandcollapse popup

; ... first you have to get $aTableData and $bTableData ...

Local $a = _ArrayExtract($aTableData, 1, UBound($aTableData) - 1, 0, 0) ; extract column 0 from array A
Local $b = _ArrayExtract($bTableData, 1, UBound($bTableData) - 1, 0, 0) ; extract column 0 from array B

_ArrayDisplay(_Separate($a, $b)) ; show result

; analyzes and split data of the 2 arrays
Func _Separate(ByRef $in0, ByRef $in1)
    $in0 = _ArrayUnique($in0, 0, Default, Default, 0)
    $in1 = _ArrayUnique($in1, 0, Default, Default, 0)
    Local $z[2] = [UBound($in0), UBound($in1)], $low = 1 * ($z[0] > $z[1]), $aTemp[$z[Not $low]][3], $aOut = $aTemp, $aNdx[3]
    For $i = 0 To $z[Not $low] - 1
        If $i < $z[0] Then $aTemp[$i][0] = $in0[$i]
        If $i < $z[1] Then $aTemp[$i][1] = $in1[$i]
    Next
    For $i = 0 To $z[$low] - 1
        $x = _ArrayFindAll($aTemp, $aTemp[$i][$low], 0, 0, 1, 0, Not $low)
        If Not @error Then ; both
            For $j = 0 To UBound($x) - 1
                $aTemp[$x[$j]][2] = 1
            Next
            $aOut[$aNdx[2]][2] = $aTemp[$i][$low]
            $aNdx[2] += 1
        Else ; only in $low
            $aOut[$aNdx[$low]][$low] = $aTemp[$i][$low]
            $aNdx[$low] += 1
        EndIf
    Next
    For $i = 0 To $z[Not $low] - 1
        If $aTemp[$i][2] <> 1 Then
            $aOut[$aNdx[Not $low]][Not $low] = $aTemp[$i][Not $low]
            $aNdx[Not $low] += 1
        EndIf
    Next
    ReDim $aOut[_ArrayMax($aNdx)][3]
    Return $aOut
EndFunc   ;==>_Separate

 

[iamtheky]

@mikell's scripting dictionary recommendation from the same thread where you dropped that is where i was headed once we got arrays

 

Edited July 13, 2018 by iamtheky

[genius257]

This seems like a problem a simple database query could solve?

if you only need to check if a solution change number is in both lists, simply create an in memory database (for example SQLite) with the two tables.

the query would then be something like:

SELECT * FROM (SELECT * FROM A UNION SELECT * FROM B) WHERE id IN (SELECT id FROM A WHERE A.id NOT IN (SELECT id FROM B) UNION SELECT id FROM B WHERE B.id NOT IN (SELECT id FROM A))

example data I've tested with:

expandcollapse popup

CREATE TABLE A( id          integer,  name    text,
                          designation text,     manager integer,
                          hired_on    date,     salary  integer,
                          commission  float,    dept    integer);

  INSERT INTO A VALUES (1,'JOHNSON','ADMIN',6,'1990-12-17',18000,NULL,4);
  INSERT INTO A VALUES (2,'HARDING','MANAGER',9,'1998-02-02',52000,300,3);
  INSERT INTO A VALUES (3,'TAFT','SALES I',2,'1996-01-02',25000,500,3);
  INSERT INTO A VALUES (4,'HOOVER','SALES I',2,'1990-04-02',27000,NULL,3);
  INSERT INTO A VALUES (5,'LINCOLN','TECH',6,'1994-06-23',22500,1400,4);
  INSERT INTO A VALUES (6,'GARFIELD','MANAGER',9,'1993-05-01',54000,NULL,4);
  INSERT INTO A VALUES (7,'POLK','TECH',6,'1997-09-22',25000,NULL,4);
  INSERT INTO A VALUES (8,'GRANT','ENGINEER',10,'1997-03-30',32000,NULL,2);
  INSERT INTO A VALUES (9,'JACKSON','CEO',NULL,'1990-01-01',75000,NULL,4);
  INSERT INTO A VALUES (10,'FILLMORE','MANAGER',9,'1994-08-09',56000,NULL,2);
  INSERT INTO A VALUES (11,'ADAMS','ENGINEER',10,'1996-03-15',34000,NULL,2);
  INSERT INTO A VALUES (12,'WASHINGTON','ADMIN',6,'1998-04-16',18000,NULL,4);
  INSERT INTO A VALUES (14,'ROOSEVELT','CPA',9,'1995-10-12',35000,NULL,1);

CREATE TABLE B( id          integer,  name    text,
                          designation text,     manager integer,
                          hired_on    date,     salary  integer,
                          commission  float,    dept    integer);

  INSERT INTO B VALUES (1,'JOHNSON','ADMIN',6,'1990-12-17',18000,NULL,4);
  INSERT INTO B VALUES (2,'HARDING','MANAGER',9,'1998-02-02',52000,300,3);
  INSERT INTO B VALUES (3,'TAFT','SALES I',2,'1996-01-02',25000,500,3);
  INSERT INTO B VALUES (4,'HOOVER','SALES I',2,'1990-04-02',27000,NULL,3);
  INSERT INTO B VALUES (5,'LINCOLN','TECH',6,'1994-06-23',22500,1400,4);
  INSERT INTO B VALUES (6,'GARFIELD','MANAGER',9,'1993-05-01',54000,NULL,4);
  INSERT INTO B VALUES (7,'POLK','TECH',6,'1997-09-22',25000,NULL,4);
  INSERT INTO B VALUES (8,'GRANT','ENGINEER',10,'1997-03-30',32000,NULL,2);
  INSERT INTO B VALUES (10,'FILLMORE','MANAGER',9,'1994-08-09',56000,NULL,2);
  INSERT INTO B VALUES (11,'ADAMS','ENGINEER',10,'1996-03-15',34000,NULL,2);
  INSERT INTO B VALUES (12,'WASHINGTON','ADMIN',6,'1998-04-16',18000,NULL,4);
  INSERT INTO B VALUES (13,'MONROE','ENGINEER',10,'2000-12-03',30000,NULL,2);
  INSERT INTO B VALUES (14,'ROOSEVELT','CPA',9,'1995-10-12',35000,NULL,1);

 

[mknope]

Quote

 

 

ArrayA.txt

ArrayB.txt

[mknope]

15 hours ago, Chimp said:

if you are interested to compare only values from the column 0, then the post you can find at this link can be handy:
for example pass to the function 2 arrays containing values from column 0 of A and B  and a 2D array will be returned where values of column 0 are those present only in array A, those in column 1 are only present in array B, and values in column 2 are present in both arrays:

expandcollapse popup

; ... first you have to get $aTableData and $bTableData ...

Local $a = _ArrayExtract($aTableData, 1, UBound($aTableData) - 1, 0, 0) ; extract column 0 from array A
Local $b = _ArrayExtract($bTableData, 1, UBound($bTableData) - 1, 0, 0) ; extract column 0 from array B

_ArrayDisplay(_Separate($a, $b)) ; show result

; analyzes and split data of the 2 arrays
Func _Separate(ByRef $in0, ByRef $in1)
    $in0 = _ArrayUnique($in0, 0, Default, Default, 0)
    $in1 = _ArrayUnique($in1, 0, Default, Default, 0)
    Local $z[2] = [UBound($in0), UBound($in1)], $low = 1 * ($z[0] > $z[1]), $aTemp[$z[Not $low]][3], $aOut = $aTemp, $aNdx[3]
    For $i = 0 To $z[Not $low] - 1
        If $i < $z[0] Then $aTemp[$i][0] = $in0[$i]
        If $i < $z[1] Then $aTemp[$i][1] = $in1[$i]
    Next
    For $i = 0 To $z[$low] - 1
        $x = _ArrayFindAll($aTemp, $aTemp[$i][$low], 0, 0, 1, 0, Not $low)
        If Not @error Then ; both
            For $j = 0 To UBound($x) - 1
                $aTemp[$x[$j]][2] = 1
            Next
            $aOut[$aNdx[2]][2] = $aTemp[$i][$low]
            $aNdx[2] += 1
        Else ; only in $low
            $aOut[$aNdx[$low]][$low] = $aTemp[$i][$low]
            $aNdx[$low] += 1
        EndIf
    Next
    For $i = 0 To $z[Not $low] - 1
        If $aTemp[$i][2] <> 1 Then
            $aOut[$aNdx[Not $low]][Not $low] = $aTemp[$i][Not $low]
            $aNdx[Not $low] += 1
        EndIf
    Next
    ReDim $aOut[_ArrayMax($aNdx)][3]
    Return $aOut
EndFunc   ;==>_Separate

 

Chimp,

I tried using that code and I am getting a bunch of errors.

 

[jchd]

AutoIt doesn't allow functions definitions to be nested.

Copy the code snippet outside of your own function definition.

[iamtheky]

sorry, misplaced this one.  If you add some of File A to File B you will see this one function as well

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

Local $aCompare1 , $aCompare2
_FileReadToArray("ArrayA.txt" , $aCompare1 , 0 , "|")
_FileReadToArray("ArrayB.txt" , $aCompare2 , 0 , "|")


Global $aBoth[0][ubound($aCompare1 , 2)]

Global $x = 0

For $i = ubound($aCompare1) - 1 to 1 step -1
$iMatch = _ArraySearch($aCompare2 , $aCompare1[$i][0])
If $iMatch <> -1 then
   _ArrayAdd($aBoth , _ArrayExtract($aCompare1 , $i , $i))
    _ArrayDelete($aCompare1 , $i)
    _ArrayDelete($aCompare2 , $iMatch)
    $x += 1
EndIf
Next

For $i = ubound($aCompare2) - 1 to 1 step -1
$iMatch = _ArraySearch($aCompare1 , $aCompare2[$i][0])
If $iMatch <> -1 then
    _ArrayAdd($aBoth , _ArrayExtract($aCompare2 , $i , $i))
    _ArrayDelete($aCompare2, $i)
    _ArrayDelete($aCompare1 ,$iMatch)
    $x += 1
EndIf
Next

_ArrayDisplay ($aCompare1, "Only in Compare 1")
_ArrayDisplay ($aCompare2, "Only in Compare 2")
_ArrayDisplay ($aBoth, "In Both")

 

Edited July 13, 2018 by iamtheky

[Gianni]

2 hours ago, mknope said:

Chimp,

I tried using that code and I am getting a bunch of errors.

... save this script along with your provided txt files

expandcollapse popup

#include <array.au3>
#include <file.au3>

_ArrayDisplay(Array_Compare())

Func Array_Compare()
; ... first load $aTableData and $bTableData from provided txt files
Local $aTableData, $bTableData
_FileReadToArray("ArrayA.txt" , $aTableData , 0 , "|")
_FileReadToArray("ArrayB.txt" , $bTableData , 0 , "|")

Local $a = _ArrayExtract($aTableData, 1, UBound($aTableData) - 1, 0, 0) ; extract column 0 from array A
Local $b = _ArrayExtract($bTableData, 1, UBound($bTableData) - 1, 0, 0) ; extract column 0 from array B

Return _Separate($a, $b) ; return the split data to the caller
EndFunc

; analyzes and split data of the 2 arrays
Func _Separate(ByRef $in0, ByRef $in1)
    $in0 = _ArrayUnique($in0, 0, Default, Default, 0)
    $in1 = _ArrayUnique($in1, 0, Default, Default, 0)
    Local $z[2] = [UBound($in0), UBound($in1)], $low = 1 * ($z[0] > $z[1]), $aTemp[$z[Not $low]][3], $aOut = $aTemp, $aNdx[3]
    For $i = 0 To $z[Not $low] - 1
        If $i < $z[0] Then $aTemp[$i][0] = $in0[$i]
        If $i < $z[1] Then $aTemp[$i][1] = $in1[$i]
    Next
    For $i = 0 To $z[$low] - 1
        $x = _ArrayFindAll($aTemp, $aTemp[$i][$low], 0, 0, 1, 0, Not $low)
        If Not @error Then ; both
            For $j = 0 To UBound($x) - 1
                $aTemp[$x[$j]][2] = 1
            Next
            $aOut[$aNdx[2]][2] = $aTemp[$i][$low]
            $aNdx[2] += 1
        Else ; only in $low
            $aOut[$aNdx[$low]][$low] = $aTemp[$i][$low]
            $aNdx[$low] += 1
        EndIf
    Next
    For $i = 0 To $z[Not $low] - 1
        If $aTemp[$i][2] <> 1 Then
            $aOut[$aNdx[Not $low]][Not $low] = $aTemp[$i][Not $low]
            $aNdx[Not $low] += 1
        EndIf
    Next
    ReDim $aOut[_ArrayMax($aNdx)][3]
    Return $aOut
EndFunc   ;==>_Separate

 

[Gianni]

10 hours ago, genius257 said:

This seems like a problem a simple database query could solve?

if you only need to check if a solution change number is in both lists, simply create an in memory database (for example SQLite) with the two tables.

the query would then be something like:

SELECT * FROM (SELECT * FROM A UNION SELECT * FROM B) WHERE id IN (SELECT id FROM A WHERE A.id NOT IN (SELECT id FROM B) UNION SELECT id FROM B WHERE B.id NOT IN (SELECT id FROM A))

Hi, @genius257 using your query on the data provided by OP seems to result in a table containing all records from both arrays... what should it return instead?

here a simple way to test SQL queries making use of an udf I've posted  some time ago. To use the SQL queries follow this simple steps:

1. download the ArraySQL.udf from this link and save it in the same directory of the following script
2. download the Sqlite3.dll from this link and also save it in the same directory (get the 32 or 64 bit version from "Precompiled Binaries for Windows" according to your AutoIt system
3. save the two provided txt files from some posts above and save both with the other stuff
4. run the following test:

   #include <file.au3>
   
   #include 'ArraySQL.au3' ; <---- get this from the following link:
   ; https://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?do=findComment&comment=1234441
   
   ; ... first load $aTableData and $bTableData from provided txt files
   Local $aTableData, $bTableData
   _FileReadToArray("ArrayA.txt", $aTableData, 0, "|")
   _FileReadToArray("ArrayB.txt", $bTableData, 0, "|")
   
   _ArrayToDbTable($aTableData, "A") ; save the first array in an SQLite table named "A"
   _ArrayToDbTable($bTableData, "B") ; save the second array in an SQLite table named "B"
   ; fields names are Column0, Column1, Column2
   
   ; SQL query by @genius257
   Local $sQuery = "SELECT * FROM (SELECT * FROM A UNION SELECT * FROM B) WHERE Column0 IN (SELECT Column0 FROM A WHERE A.Column0 NOT IN (SELECT Column0 FROM B) UNION SELECT Column0 FROM B WHERE B.Column0 NOT IN (SELECT Column0 FROM A));"
   
   Local $aResult = _ArrayFromSQL($sQuery) ; get the result of the SQL query
   _ArrayDisplay($aResult, @error) ; show result

    

 

[mknope]

I was able to get it to work with the code below and send it to excel. How would I go about adding a row to add column headers?

expandcollapse popup

;; analyzes and split data of the 2 arrays
Func _Separate(ByRef $in0, ByRef $in1)
    $in0 = _ArrayUnique($in0, 0, Default, Default, 0)
    $in1 = _ArrayUnique($in1, 0, Default, Default, 0)
    Local $z[2] = [UBound($in0), UBound($in1)], $low = 1 * ($z[0] > $z[1]), $aTemp[$z[Not $low]][3], $aOut = $aTemp, $aNdx[3]
    For $i = 0 To $z[Not $low] - 1
        If $i < $z[0] Then $aTemp[$i][0] = $in0[$i]
        If $i < $z[1] Then $aTemp[$i][1] = $in1[$i]
    Next
    For $i = 0 To $z[$low] - 1
        $x = _ArrayFindAll($aTemp, $aTemp[$i][$low], 0, 0, 1, 0, Not $low)
        If Not @error Then ; both
            For $j = 0 To UBound($x) - 1
                $aTemp[$x[$j]][2] = 1
            Next
            $aOut[$aNdx[2]][2] = $aTemp[$i][$low]
            $aNdx[2] += 1
        Else ; only in $low
            $aOut[$aNdx[$low]][$low] = $aTemp[$i][$low]
            $aNdx[$low] += 1
        EndIf
    Next
    For $i = 0 To $z[Not $low] - 1
        If $aTemp[$i][2] <> 1 Then
            $aOut[$aNdx[Not $low]][Not $low] = $aTemp[$i][Not $low]
            $aNdx[Not $low] += 1
        EndIf
    Next
    ReDim $aOut[_ArrayMax($aNdx)][3]
    Return $aOut
    
    Func Send_to_excel()
    ; List all the files in the current script directory.
Local $aScriptDir = _FileListToArray(@ScriptDir)

; Create a file in the users %TEMP% directory.
Local $sFilePath = @TempDir & "\Examples.csv"

; Write array to a file by passing the file name.
_FileWriteFromArray($sFilePath, _Separate($a, $b),0,Default,",")

; Display the file.
ShellExecute($sFilePath)
EndFunc

work with the code below

[genius257]

1 minute ago, Chimp said:

Hi, @genius257 using your query on the data provided by OP seems to result in a table containing all records from both arrays... what should it return instead?

Hi @Chimp

If the solution change number numbers are in the id column, only matches that does not exist in the other should be returned.

If that is not the case, please let me know, and I'll try and provide a working example with the test data provided by @mknope.

[iamtheky]

i think you are both right, none of the data provided by the OP meets that criteria.  If you copy some lines from A into B then every solution here is steps in the right direction.

[Gianni]

7 minutes ago, genius257 said:

If the solution change number numbers are in the id column, only matches that does not exist in the other should be returned.

If that is not the case, please let me know, and I'll try and provide a working example with the test data provided by @mknope.

hi @genius257

From my test I get a table containing all records from both tables....

[iamtheky]

Quote

only matches that does not exist in the other should be returned

so @Chimp's result would be expected with input where A and B do not share any commonalities (like the files provided...)?

Do you get two sets of the headers if you include row 0?

Edited July 13, 2018 by iamtheky