Jump to content
mknope

Array Comparison - (Moved)

Recommended Posts

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. 

image.thumb.png.a714555dfa3aeb6cad91df33984e40a5.png

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

 

Share this post


Link to post
Share on other sites
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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
water

Welcome to AutoIt and the forum!

Maybe this thread gets you started:

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Or this one:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
mknope

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

image.png.c62757d14614dd17d90304717d5f4b02.png

This is how each array looks before the comparison:

Array A:

image.thumb.png.83548f12fa3e294539388355e0b8144f.png
 

Array B: 

image.thumb.png.308b3c696f0ef94ff5c177e367310991.png

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

 

Share this post


Link to post
Share on other sites
iamtheky
Posted (edited)

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 by iamtheky

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
Chimp

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:

; ... 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

 


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
iamtheky
Posted (edited)

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

 

Edited by iamtheky

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
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:

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);

 

Share this post


Link to post
Share on other sites
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:

; ... 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.

image.thumb.png.5a1a351ec532c2d3bd436cf51f0e42be.png

 

Share this post


Link to post
Share on other sites
jchd

AutoIt doesn't allow functions definitions to be nested.

Copy the code snippet outside of your own function definition.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
iamtheky
Posted (edited)

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 by iamtheky

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
Chimp
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

#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

 


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
Chimp
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

     

 


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
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?

;; 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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
Chimp
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....


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
iamtheky
Posted (edited)
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 by iamtheky
  • Like 1

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×