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

So, basically you want to list the unique account numbers from `$f` that don't have an identical record in `$table4`? — Ansgar Wiechers, Aug 08 '17 at 14:33

iRon · Accepted Answer · 2017-08-08T17:28:48.827

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

edited Aug 08 '17 at 17:28

answered Aug 08 '17 at 15:19

iRon

20,463
10
53
79

Yeah, occupant_code is no longer a constraint, and am matching the CSV to the Table4, so the result would be one instance of account: 67890 – Simon Yo Aug 08 '17 at 15:48
In that case there is no question of joining as there is nothing in the result from the `$table4`. In other words, `$table4` is only used to select the records in the `$f` table. I have change my answer accordingly. – iRon Aug 08 '17 at 17:10
Didn't know you could write where statements like that. Thank you, this is perfect. – Simon Yo Aug 08 '17 at 18:06

score 0 · Answer 2 · answered Aug 08 '17 at 15:39

Build a reference list from the records in $table4

$ref = $table4 | ForEach-Object {
    $_.Account_no, $_.occupant_code, $_.current_read_date -join ','
}

then filter the records from $f by that reference list:

$accounts = Import-Csv $f | Where-Object {
    $ref -notcontains ($_.Account_no, $_.occupant_code, $_.current_read_date -join ',')
} | Select-Object -Expand Account_no -Unique

Check if Two Values in Array / Data Table match

2 Answers2