Process and compare records in single collection without repeating

[Honza]

Hello,

can you advise what should I search for in documentation. I do not know how to call what I am trying to do.
I have a $collection with $record and need to process each to $records a compare it. But I do not want to compare same records and repeat the comparison.
Dummy example of code:

PowerShell

foreach ($record in $collection) { $start = $record.time $label1 = $record.label foreach ($otherrecord in $collection) { $end = $otherrecord.time $label2 = $otherrecord.label $howlong = (New-TimeSpan -Start $start -End $end).totalhours if ($howlong -gt 24) { write-host "$label1 and $label2 with more than 24 hours time difference." } } }

1 2 3 4 5 6 7 8 9 10 11 12

foreach ($record in $collection) { $start = $record.time $label1 = $record.label foreach ($otherrecord in $collection) { $end = $otherrecord.time $label2 = $otherrecord.label $howlong = (New-TimeSpan -Start $start -End $end).totalhours if ($howlong -gt 24) { write-host "$label1 and $label2 with more than 24 hours time difference." } } }

With the code above, it would compare:

A-A (do not want)
A-B
A-C
B-A (do not want)
B-B (do not want)
B-C
C-A (do not want)
C-B (do not want)
C-C (do not want)

It does not matter with 3 records, but I have collection with 80 000+ records. Is there a function or some trick for this? What should I look for?

Thank you

Honza

• This topic was modified 1 month, 2 weeks ago by Honza.

[Mike R. Participant Topics: 5 Replies: 249 Points: 985Rank: Major Contributor]

How about this?

PowerShell

foreach ($item in $collection){ $collection | Where-Object {($_.time - $item.time).TotalHours -gt 24} | foreach-object {"{0} and {1} with more than 24 hours" -f $item.label, $_.label} }

1 2 3 4 5

foreach ($item in $collection){     $collection |         Where-Object {($_.time - $item.time).TotalHours -gt 24} |             foreach-object {"{0} and {1} with more than 24 hours" -f $item.label, $_.label}   }

 

[Rob Simmers Participant Topics: 17 Replies: 1951 Points: 3,986Rank: Community Hero]

You have two sources with matching labels? Normally you would want to do some kind of join operation on the other recordset (e.g. Label) and build an object:

PowerShell

$record = @() $record += [pscustomobject]@{Label='A-A';Time=(Get-Date)} $record += [pscustomobject]@{Label='A-B';Time=(Get-Date)} $otherrecord = @() $otherrecord += [pscustomobject]@{Label='A-A';Time=(Get-Date).AddDays(2)} $otherrecord += [pscustomobject]@{Label='A-B';Time=(Get-Date).AddHours(2)} $results = foreach ($r in $record) { $match = $otherRecord | Where{$_.Label -eq $r.Label} $r | Select-Object Label, @{Name='StartTime';Expression={$_.Time}}, @{Name='EndTime';Expression={$match.Time}}, @{Name='Hours';Expression={[math]::Round((New-TimeSpan -Start $_.Time -End $match.Time).TotalHours)}} } $results | Where{$_.Hours -gt '24'}

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

$record = @() $record += [pscustomobject]@{Label='A-A';Time=(Get-Date)} $record += [pscustomobject]@{Label='A-B';Time=(Get-Date)}   $otherrecord = @() $otherrecord += [pscustomobject]@{Label='A-A';Time=(Get-Date).AddDays(2)} $otherrecord += [pscustomobject]@{Label='A-B';Time=(Get-Date).AddHours(2)}   $results = foreach ($r in $record) {     $match = $otherRecord | Where{$_.Label -eq $r.Label}       $r | Select-Object Label,                        @{Name='StartTime';Expression={$_.Time}},                        @{Name='EndTime';Expression={$match.Time}},                        @{Name='Hours';Expression={[math]::Round((New-TimeSpan -Start $_.Time -End $match.Time).TotalHours)}} }   $results | Where{$_.Hours -gt '24'}

Once you have the object, you can execute a filter on the results:

PowerShell

PS C:\Users\rasim> $results Label StartTime EndTime Hours ----- --------- ------- ----- A-A 12/8/2020 3:20:14 PM 12/10/2020 3:20:14 PM 48 A-B 12/8/2020 3:20:14 PM 12/8/2020 5:20:14 PM 2 PS C:\Users\rasim> c:\Users\rasim\Desktop\temp.ps1 Label StartTime EndTime Hours ----- --------- ------- ----- A-A 12/8/2020 3:25:52 PM 12/10/2020 3:25:52 PM 48

1 2 3 4 5 6 7 8 9 10 11 12 13

PS C:\Users\rasim> $results     Label StartTime            EndTime               Hours ----- ---------            -------               ----- A-A   12/8/2020 3:20:14 PM 12/10/2020 3:20:14 PM    48 A-B   12/8/2020 3:20:14 PM 12/8/2020 5:20:14 PM      2   PS C:\Users\rasim> c:\Users\rasim\Desktop\temp.ps1   Label StartTime            EndTime               Hours ----- ---------            -------               ----- A-A   12/8/2020 3:25:52 PM 12/10/2020 3:25:52 PM    48

While you can output a string or log, you are then reading it manually. If you use an object approach, you can see all of that data and can do comparisons and exports such as a csv.

[AdminOfThings45 Participant Topics: 1 Replies: 95 Points: 435Rank: Contributor]

To me it is simplest to use a Queue object. You can Dequeue() (remove) the first object during each run to ensure no repeats.

PowerShell

$queue = [System.Collections.Queue]::new($collection) while ($queue.Count) { $top = $queue.Dequeue() $queue.ToArray() | Foreach-Object { if (($top.Time - $_.Time).Duration().TotalSeconds -gt 86400) { "{0} and {1} with more than 24 hours time difference" -f $top.label,$_.Label } } }

1 2 3 4 5 6 7 8 9 10

$queue = [System.Collections.Queue]::new($collection)   while ($queue.Count) {     $top = $queue.Dequeue()     $queue.ToArray() | Foreach-Object {         if (($top.Time - $_.Time).Duration().TotalSeconds -gt 86400) {             "{0} and {1} with more than 24 hours time difference" -f $top.label,$_.Label         }     } }

This is not going to be an efficient process without have presorted data or using another tool built for bulk comparisons/joins.

[Honza Participant Topics: 3 Replies: 7 Points: 53Rank: Member]

Thank you guys.

I think I found solution on my own. It is primitive, but it works.

PowerShell

$collection2 = $collection1 $collection2 = {$collection2}.invoke() foreach ($collrow1 in $collection1) { ..... if ($null -ne $collection2) { $collection2.RemoveAt(0) foreach ($collrow2 in $collection2) { ... compare values ... } } }

1 2 3 4 5 6 7 8 9 10 11

$collection2 = $collection1 $collection2 = {$collection2}.invoke() foreach ($collrow1 in $collection1) { ..... if ($null -ne $collection2) { $collection2.RemoveAt(0) foreach ($collrow2 in $collection2) { ... compare values ... } } }

 

Thank you all. I am going to check your ideas anyway.

Honza

 

[Author]

Posts