How to use Group-Object on this?

Question

I am trying to get all the accounts from $f which do not match the accounts in $table4 into $accounts. But I need to also check if the occupancy number matches or not.

CSV $f:

Account_no |occupant_code
-----------|------------
12345      |    1
67890      |    2
45678      |    3

DataTable $table4

Account_no |occupant_code
-----------|------------
12345      |   1
67890      |   1
45678      |   3

Current code:

$accounts = Import-Csv $f |
            select account_no, occupant_code |
            where { $table4.account_no -notcontains $_.account_no }

What this needs to do is to check that occupant_code doesn't match, i.e.:

• 12345: account and occupant from $f and $table4 match; so it's ignored
• 67890: account matches $table4, but occupancy_code does not match, so it is added to $accounts.

Current result:
Desired result: 67890

I believe I need to use Group-Object, but I do not know how to use that correctly.

I tried:

Import-Csv $f |
    select account_no, occupant_code |
    Group-Object account_no |
    Where-Object { $_.Group.occupant_code -notcontains $table4.occupant_code }

Answer 1

Compare-Object?

csv1.csv:

Account_no,occupant_code
12345,1
67890,2
45678,3

csv2.csv:

Account_no,occupant_code
12345,1
67890,1
45678,3

PowerShell command:

Compare-Object (Import-Csv .\csv1.csv) (Import-Csv .\csv2.csv) -Property occupant_code -PassThru

Output:

Account_no occupant_code SideIndicator
---------- ------------- -------------
67890      1             =>
67890      2             <=

Answer 2

An alternative to Bill's suggestion would be to fill a hashtable with your reference data ($table4) and look up the occupant_code value for each account from $f, assuming that your account numbers are unique:

$ref = @{}
$table4 | ForEach-Object {
    $ref[$_.Account_no] = $_.occupant_code
}

$accounts = Import-Csv $f |
            Where-Object { $_.occupant_code -ne $ref[$_.Account_no] } |
            Select-Object -Expand Account_no

Answer 3

$f | InnerJoin $table4 {$Left.Account_no -eq $Right.Account_no -and $Left.occupant_code -ne $Right.occupant_code} @{Account_no = {$Left.$_}} | Format-Table

Result:

occupant_code Account_no
------------- ----------
{2, 1}        67890

For details see: In Powershell, what's the best way to join two tables into one?

Answer 4

In addition to all the other answers, you might be able to leverage the IndexOf() method on arrays

$services = get-service
$services.name.IndexOf("xbgm")
240

I am on a tablet right now and don't have a handy way to test it, but something along these lines might work for you:

$table4.account_no.IndexOf($_.account_no)

should fetch the index your account_no lives in for $table 4, so you could jam it all into one ugly pipe:

$accounts = Import-Csv $f | select account_no, occupant_code |
            where { ($table4.account_no -notcontains $_.account_no) -or ($table4[$table4.account_no.IndexOf($_.account_no)].occupant_code -ne $_.occupant_code) }

An inner join or a normal loop might just be cleaner though, especially if you want to add some other stuff in. Since someone posted an innerjoin, you could try a loop like:

$accounts = new-object System.Collections.ArrayList
$testSet = $table4.account_no
foreach($myThing in Import-Csv $f)
{
    if($myThing.account_no -in $testSet )
    {
        $i = $testSet.IndexOf($myThing.account_no)
        if($table4[$i].occupant_code -eq $myThing.occupant_code) {continue}
    }

    $accounts.add($myThing)
}

Edit for OP, he mentioned $table4 is a data.table There is probably a much better way to do this, as I haven't used data.table before, but this seems to work fine:

$table = New-Object system.Data.DataTable
$col1 = New-Object system.Data.DataColumn Account_no,([string])
$col2 = New-Object system.Data.DataColumn occupant_code,([int])
$table.columns.add($col1)
$table.columns.add($col2)

$row = $table.NewRow()
$row.Account_no = "12345"
$row.occupant_code = 1
$table.Rows.Add($row)

$row = $table.NewRow()
$row.Account_no = "67890"
$row.occupant_code = 1
$table.Rows.Add($row)

$row = $table.NewRow()
$row.Account_no = "45678"
$row.occupant_code = 3
$table.Rows.Add($row)

$testList = @()
$testlist += [pscustomobject]@{Account_no = "12345"; occupant_code = 1}
$testlist += [pscustomobject]@{Account_no = "67890"; occupant_code = 2}
$testlist += [pscustomobject]@{Account_no = "45678"; occupant_code = 3}

$accounts = new-object System.Collections.ArrayList
$testSet = $table.account_no
foreach($myThing in $testList)
{
    if($myThing.account_no -in $testSet )
    {
        $i = $testSet.IndexOf($myThing.account_no)
        if($table.Rows[$i].occupant_code -eq $myThing.occupant_code) {continue}
    }

    $accounts.add($myThing) | out-null
}

$accounts