compare two multip column araays

This topic contains 10 replies, has 5 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 months ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #39315
    Profile photo of Siebrand
    Siebrand
    Participant

    I've two arrays; 1. $A
    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761

    and $B;

    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    AF553CD0-F24F-44F1 679F7BDF-788D-4593
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C

    I want to get the line from Array $B which does not exist in $A,

    i first tried it with a $B | where {$A -notcontains $_}, but this didn't work well. Could you help me out?

    #39316
    Profile photo of random commandline
    random commandline
    Participant

    I have two options below.

    $array1 = @"
    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    "@ -split "`n"
    
    $array2 = @"
    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    AF553CD0-F24F-44F1 679F7BDF-788D-4593
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    "@ -split "`n"
    
    foreach ($item in $array2){
        If ($array1 -notcontains $item){$item}}
    
    Compare-Object -ReferenceObject $array2 -DifferenceObject $array1
    # Results:
    # InputObject                                       SideIndicator
    # -----------                                       -------------
    # AF553CD0-F24F-44F1 679F7BDF-788D-4593             <=
    
    #39318
    Profile photo of Dan Potter
    Dan Potter
    Participant

    That's a neat way to make arrays 🙂

    #43548
    Profile photo of Siebrand Feenstra
    Siebrand Feenstra
    Participant

    thx! sorry for the delay. this indeed works . however. how can I go back to a array table?
    when the result is like this;

    @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADE}
    @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADF}
    
    #44639
    Profile photo of Siebrand
    Siebrand
    Participant

    Anyone? The split works fine to be able to compare. But I need to revert it back to an array, to work with the different values, or remove or add columns, values to the array

    #44673
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You have 2 arrays:

    $array1 = @()
    $array1 += [pscustomobject]@{PackageID="58E4DB62-67D3-40CA";VersionId="122600F0-34E0-4F3C"}
    $array1 += [pscustomobject]@{PackageID="AF553CD0-F24F-44F1";VersionId="E69AE4CF-C3E1-410C"}
    $array1 += [pscustomobject]@{PackageID="AE67BEFF-ECB8-42C7";VersionId="E77EC859-E340-4761"}
    
    $array2 = @()
    $array2 += [pscustomobject]@{PackageID="58E4DB62-67D3-40CA";VersionId="122600F0-34E0-4F3C"}
    $array2 += [pscustomobject]@{PackageID="AE67BEFF-ECB8-42C7";VersionId="E77EC859-E340-4761"}
    $array2 += [pscustomobject]@{PackageID="AF553CD0-F24F-44F1";VersionId="679F7BDF-788D-4593"}
    $array2 += [pscustomobject]@{PackageID="AF553CD0-F24F-44F1";VersionId="E69AE4CF-C3E1-410C"}
    

    I'm not sure what the end result is that you are looking for. Here are some options to work with the data. If you are trying to create a final object, you can do something like this:

    #Join the arrays and get unique VersionID
    $array3 = $array1 + $array2
    $array3 | Sort-Object VersionId -Unique
    
    
    PackageID          VersionId         
    ---------          ---------         
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 679F7BDF-788D-4593
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    

    If you simply want the difference, you can do something like this:

    #Get the difference item and add it to a new object
    $array4 = Compare-Object -ReferenceObject $array1 -DifferenceObject $array2 -PassThru | foreach {
        $_ | Select PackageID, VersionID
    }
    
    PS C:\Users\Rob> $array4
    
    PackageID          VersionId         
    ---------          ---------         
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    

    If you want to add it to an existing array, such as array 1...

    PS C:\Users\Rob> #Join the difference to the first array
    $array1 + $array4
    
    
    PackageID          VersionId         
    ---------          ---------         
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    
    #44676
    Profile photo of Siebrand
    Siebrand
    Participant
    PS C:\Users\n002323b.PPGEUR.000> $ActivePackagesOnDiskArray
    
    PackageId                                                                           Versionid                                                                         
    ---------                                                                           ---------                                                                         
    58E4DB62-67D3-40CA-9051-31FD54A0AB1A                                                58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                              
    58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                                122600F0-34E0-4F3C-AA30-3BE7A0733EBA                                              
    58E4DB62-67D3-40CA-9051-31FD54A0AB1E                                                58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                              
    AE67BEFF-ECB8-42C7-AB01-C14698F415D9                                                D3629331-A90E-4E87-92BC-32D7F48BF37E                                              
    C3EB6BB1-4294-4634-BAAE-EE974212EADE                                                CD3EA732-7078-4B29-97A4-277C830AFE94                                              
    E0F1D187-93A6-42D1-96BF-72735E442B65                                                625EED06-E466-4C50-B264-C9D6FE6B8314                                              
    
    
    
    PS C:\Users\n002323b.PPGEUR.000> $ActivePackagesArray
    
    PackageId                                                                           Versionid                                                                         
    ---------                                                                           ---------                                                                         
    58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                                122600F0-34E0-4F3C-AA30-3BE7A0733EBA                                              
    E0F1D187-93A6-42D1-96BF-72735E442B65                                                625EED06-E466-4C50-B264-C9D6FE6B8314                                              
    C3EB6BB1-4294-4634-BAAE-EE974212EADE                                                CD3EA732-7078-4B29-97A4-277C830AFE94                                              
    AE67BEFF-ECB8-42C7-AB01-C14698F415D9                                                D3629331-A90E-4E87-92BC-32D7F48BF37E                                              
    
    
    
    PS C:\Users\n002323b.PPGEUR.000> Compare-Object -ReferenceObject $ActivePackagesArray -DifferenceObject $ActivePackagesOnDiskArray -PassThru | foreach {
        $_ | Select PackageID, VersionID
    }
    
    PackageId                                                                           Versionid                                                                         
    ---------                                                                           ---------                                                                         
    C3EB6BB1-4294-4634-BAAE-EE974212EADE                                                CD3EA732-7078-4B29-97A4-277C830AFE94                                              
    E0F1D187-93A6-42D1-96BF-72735E442B65                                                625EED06-E466-4C50-B264-C9D6FE6B8314                                              
    
    

    The issue with directly comparing a multi columns array is that It give me the wrong lines back, as shown above. For that, the suggestion to split it using '`n' worked. It give me the correct lines back. But then I've formatted output like;

    @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADE}
    @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADF}
    

    I need that to revert that back to an array table. to better work with that.

    #44747
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    To answer your question, those are hash tables. A Powershell Object (PSObject) is an array of hash tables. If you look at the previous example, when I created the mock objects, I used a typename of [pscustomobject], this a simple way to convert a hashtable to a PSObject. Either method below would convert the hash table to a PSObject.

    $props = @{PACKAGEID="C3EB6BB1-4294-4634-BAAE-EE974212EADF"; VERSIONID="C3EB6BB1-4294-4634-BAAE-EE974212EADE"}
    
    New-Object -TypeName PSObject -Property $props
    
    #or
    
    [pscustomobject]$props
    

    Using the `n is basically appears to make the entire line a compare string. It's a common way in SQL to compare multiple rows as a basic unique identifier. The `n seems a bit kludgy to me. Possibly try this method which uses a calculated expression to accomplish the same thing (using my array examples previously):

    $array1 = $array1 | Select PackageID, VersionID, @{Name="CompareString";Expression={"{0}{1}" -f $_.PackageID, $_.VersionID}}
    $array2 = $array2 | Select PackageID, VersionID, @{Name="CompareString";Expression={"{0}{1}" -f $_.PackageID, $_.VersionID}}
    
    Compare-Object -ReferenceObject $array1 -DifferenceObject $array2 -Property CompareString -PassThru |
    Select PackageID, VersionID
    
    #44931
    Profile photo of Siebrand
    Siebrand
    Participant

    The calculated expression works beautifully. a nice creative solution. thanks for that!

    #45022
    Profile photo of Siebrand
    Siebrand
    Participant

    One open issue still,

    I have an array

    PS G:\> $NotMatchingArrayItems
    
    PackageID                                                                 Versionid                                                                 CompareString                                                           
    ---------                                                                 ---------                                                                 -------------                                                           
    29E49811-1214-4BBF-84CD-8AE9C5A4809F                                      E8E0CD14-469F-4FEC-AB07-A6393FC478F5                                      29E49811-1214-4BBF-84CD-8AE9C5A4809F-E8E0CD14-469F-4FEC-AB07-A6393FC4...
    4F30650F-3F12-4D8E-9333-40F581CA431E                                      64F2D58F-8349-4A46-B1E0-220621F07B2F                                      4F30650F-3F12-4D8E-9333-40F581CA431E-64F2D58F-8349-4A46-B1E0-220621F0...
    58E4DB62-67D3-40CA-9051                                                   58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                      58E4DB62-67D3-40CA-9051-58E4DB62-67D3-40CA-9051-31FD54A0AB1D            
    E0F1D187-93A6-42D1-96BF-72735E442B65                                      625EED06-E466-4C50-B264-C9D6FE6B8314                                      E0F1D187-93A6-42D1-96BF-72735E442B65-625EED06-E466-4C50-B264-C9D6FE6B...
    EF905FCD-E406-4FC3-9393-C421936419BE                                      7DFA2667-1DBC-453D-AD64-29C17A8E7081                                      EF905FCD-E406-4FC3-9393-C421936419BE-7DFA2667-1DBC-453D-AD64-29C17A8E...
    

    and another one '$AppvServerPackages';

    PackageGuid                                                                         Name                                                                              
    -----------                                                                         ----                                                                              
    29e49811-1214-4bbf-84cd-8ae9c5a4809f                                                CDM_Light_1.4.7                                                                   
    ef905fcd-e406-4fc3-9393-c421936419be                                                DWG_TrueView_2016                                                                 
    e0f1d187-93a6-42d1-96bf-72735e442b65                                                Gemba_OEE_Client_UK                                                               
    

    And now I want the $NotMatchingArrayItems array to be supplemented with an extra column 'Name' where I add the corresponding Name from the array $AppvServerPackages, when then PackageId and Packageguid match. I used the following, which didn't work...

    PS G:\> $NotMatchingArrayItems | select PackageID, VersionID, @{Name="Name";Expression={"{0}" -f $(%{$AppvServerPackages | where {$NotMatchingArrayItems.Packageid -like $_.Packageguid}}).Name}}
    
    
    PackageID                                                                 Versionid                                                                 Name                                                                    
    ---------                                                                 ---------                                                                 ----                                                                    
    29E49811-1214-4BBF-84CD-8AE9C5A4809F                                      E8E0CD14-469F-4FEC-AB07-A6393FC478F5                                      CDM_Light_1.4.7                                                         
    4F30650F-3F12-4D8E-9333-40F581CA431E                                      64F2D58F-8349-4A46-B1E0-220621F07B2F                                      CDM_Light_1.4.7                                                         
    58E4DB62-67D3-40CA-9051                                                   58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                      CDM_Light_1.4.7                                                         
    E0F1D187-93A6-42D1-96BF-72735E442B65                                      625EED06-E466-4C50-B264-C9D6FE6B8314                                      CDM_Light_1.4.7                                                         
    EF905FCD-E406-4FC3-9393-C421936419BE                                      7DFA2667-1DBC-453D-AD64-29C17A8E7081                                      CDM_Light_1.4.7                                                         
    

    Suggestions?

    • This reply was modified 3 months ago by Profile photo of Siebrand Siebrand.
    #45113
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    There are numerous ways to do it. I'll start with a "it depends". If you're dealing with a couple of records, you can do something simple, again with calculated expressions:

    $array1 = @()
    $array1 += [pscustomobject]@{PackageID="29E49811-1214-4BBF-84CD-8AE9C5A4809F";VersionId="122600F0-34E0-4F3C"}
    $array1 += [pscustomobject]@{PackageID="4F30650F-3F12-4D8E-9333-40F581CA431E";VersionId="E69AE4CF-C3E1-410C"}
    $array1 += [pscustomobject]@{PackageID="E0F1D187-93A6-42D1-96BF-72735E442B65";VersionId="E77EC859-E340-4761"}
    $array1 += [pscustomobject]@{PackageID="EF905FCD-E406-4FC3-9393-C421936419BE";VersionId="E77EC859-E340-4761"}
    
    $array2 = @()
    $array2 += [pscustomobject]@{PackageGUID="29e49811-1214-4bbf-84cd-8ae9c5a4809f";Name="CDM_Light_1.4.7"}
    $array2 += [pscustomobject]@{PackageGUID="ef905fcd-e406-4fc3-9393-c421936419be";Name="DWG_TrueView_2016 "}
    $array2 += [pscustomobject]@{PackageGUID="e0f1d187-93a6-42d1-96bf-72735e442b65";Name="Gemba_OEE_Client_UK"}
    
    $final = $array1 |
             Select PackageID, VersionID, @{Name="Name";Expression={$pkgID = $_.PackageID;$array2 | Where {$_.PackageGUID -eq $pkgID} | Select -ExpandProperty Name}}
    
    $final
    

    Your results would look something like this:

    PackageID                            VersionId          Name               
    ---------                            ---------          ----               
    29E49811-1214-4BBF-84CD-8AE9C5A4809F 122600F0-34E0-4F3C CDM_Light_1.4.7    
    4F30650F-3F12-4D8E-9333-40F581CA431E E69AE4CF-C3E1-410C                    
    E0F1D187-93A6-42D1-96BF-72735E442B65 E77EC859-E340-4761 Gemba_OEE_Client_UK
    EF905FCD-E406-4FC3-9393-C421936419BE E77EC859-E340-4761 DWG_TrueView_2016  
    

    Note if the lookup fails, you simply get a null value. If you are working with large data tables, you should leverage Join-Object, which is a function that several folks have written:

    Cookie Monster
    Powershell Team

    I've used Join-Object for 20-30k records. If you're dealing with extremely large datasets, then you should leverage SQL to do complex joins.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.