Check if Two Values in Array / Data Table match

Question

I put this question on here before, but I missed an important detail which causes huge issue. There will be duplicate account numbers. So I'm doing it by current_read_date now to avoid duplicates in account number. To ensure values being added to $accounts are new from the CSV.

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 current_read_date matches or not.

CSV into Array $f:

Account_no |occupant_code|current_read_date
-----------|-------------|-----------------
12345      |    1        |  7/17/2017 15:32:00 AM
67890      |    2        |  7/17/2017 12:00:00 AM
45678      |    3        |  7/17/2017 12:00:00 AM

DataTable $table4

Account_no |occupant_code|current_read_date
-----------|-------------|-----------------
12345      |   1         | 7/17/2017 12:00:00 AM
12345      |   1         | 7/17/2017 15:32:00 AM
67890      |   1         | 7/17/2017 13:00:00 AM
67890      |   1         | 7/17/2017 22:00:00 AM
45678      |   3         | 7/17/2017 12:00:00 AM

Desired result:

$accounts = 
    67890      |    2        |  7/17/2017 12:00:00 AM

Current code:

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

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

• 12345: account and date from $f and $table4 match; so it's ignored
• 67890: account matches $table4, but the current_read_date does not match, so it is a new value, thus it is added to $accounts.

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.current_read_date -notcontains $table4.current_read_date }

This is the previous question:
How to use Group-Object on this?

All the answers here failed because I forgot to provide the information that account_no is not unique; there will be frequent duplicates.

All assistance would be greatly appreciated, I've been stuck on this for awhile.

I've also tried this

$testList = @()
$testList = Import-Csv $f | select account_no, occupant_code, current_read_date, current_reading
$accounts = new-object System.Collections.ArrayList

$testSet = $table4
foreach($myThing in $testList)
{
    if($myThing.account_no -in $testSet.account_no )
    {
    foreach($ts in $testSet)
     { 
     if ($myThing.account_no -match $ts.account_no -and $myThing.occupant_code -match $ts.occupant_code)
        {
     $ts.account_no 
     $ts.occupant_code

     }

     else {
    $accounts.add($myThing) | out-null
    write-host $mything
        }
    }
}

This fails because it goes through each number, therefore, 12345 will be checked against 67890, and will added 12345 to the $accounts list, even though it already exists, because I cannot compare each individually at a time with table4.

Thanks

Answer 1

$accounts = $f | Where {
    $Record = $_
    $AccNo = $table4 | Where {$_.Account_no -eq $Record.Account_no}
    !($AccNo | Where {$_.current_read_date -eq $Record.current_read_date})
}
$accounts | Format-Table

Result:

Account_no occupant_code current_read_date
---------- ------------- -----------------
67890      2             7/17/2017 12:00:00 AM