使用多个键从标签可获得最大的值

Question

我有一个看起来像这样的CSV：

Record Key          Sequence Number Transportation Segment Number   Arrival Date
18010312076777      1               1                               01/16/2018
18010312076777      1               2                               01/17/2018
18010312076867      1               1                               01/16/2018
18010312076867      1               2                               01/17/2018
18010312078634      1               1                               01/16/2018
18010312078634      1               2                               01/18/2018
18010912098476      1               1                               1/10/2018
18010912098476      1               2                               1/10/2018
18010912098476      1               3                               1/11/2018
18010912098476      1               4                               1/11/2018
18010912099327      1               1                               01/16/2018
18010912099327      1               2                               01/17/2018
18010912101156      1               1                               01/16/2018
18010912101156      1               2                               01/17/2018

我已经使用Import-CSV CMDLET将其作为标签。这很好。

我正在尝试基于以下逻辑创建另一个标签：对于每个唯一的记录键和序列编号组合，返回最大的传输段编号及其相应的到达日期

例如，第一个记录键18010312076777被列出了两次，均为1个序列号(尽管序列号可能是任何值(。最大的运输段编号为2。

记录密钥18010912098476有四个条目，但是我们只对具有传输段编号的条目感兴趣。其中序列号= 1 和序列编号= 2。

因此，新的Hashtable应该包含：

Record Key          Sequence Number Transportation Segment Number   Arrival Date
18010312076777      1               2                               01/17/2018
18010312076867      1               2                               01/17/2018
18010312078634      1               2                               01/18/2018
18010912098476      1               4                               1/11/2018
18010912099327      1               2                               01/17/2018
18010912101156      1               2                               01/17/2018

我已经编写了此代码

function New-LastSegments {
  # For each unique Record-Key + Sequence number, return the largest Transportation Segment Number and the corresponding Arrival Date
  # Create a new hashtable
  $global:last_segment_reference = @{};
  # Get a list of unique record keys to search on
  $record_keys = $global:transeg_table | Select-Object "Record Key", "Sequence Number" -Unique | Out-String;
  # Step through the record keys
  $record_keys | ForEach-Object ({
     # New row
     $new_segment_reference = @{};
     # Get the last transportation segment number that matches our unique Record Key and Sequence Number
     $transportation_segment_number = $global:transeg_table |
      Where-Object ({$global:transeg_table.$('Record Key') -eq $_.$('Record Key')}) -and ({$global:transeg_table.$('Sequence Number') -eq $_.$('Sequence Number')}) | ForEach-Object $global:transeg_table.$('Transportation Segment Number') |  Select-Object -last 1
     $new_segment_reference | Add-Member -Name "Record Key" -MemberType NoteProperty -Value $_.$('Record Key');
     $new_segment_reference | Add-Member -Name "Sequence Number" -MemberType NoteProperty -Value $_.$('Sequence Number');
     $new_segment_reference | Add-Member -Name "Transportation Segment Number" -MemberType NoteProperty -Value $_.$transportation_segment_number;
     # I think this needs to be set at the same time as the Transportation Segment Number
     $new_segment_reference | Add-Member -Name "Arrival Date" -MemberType NoteProperty -Value $_.$('Arrival Date');
     # Add the new row to the new hashtable
     $global:last_segment_reference += $new_segment_reference;
 })
}

我得到

" wery-object：找不到匹配参数的参数 名称'和'。"

和

"属性'记录密钥'在此对象上找不到。验证 属性存在。"

任何人可以帮助我使用逻辑和语法来产生此标签吗？我只有一个键可以使用其他键，但是我遇到了多个键遇到困难。

Answer 1

在这里，我刚刚按前三列(Record Key，Sequence Number和Transportation Segment Number(对表进行了排序做出假设(。然后，我已经根据Record Key和Sequence Number对表数据进行了分组，并选择了每个组中的最后一项：

$groups = ($global:transeg_table | sort -Property "Record Key","Sequence Number","Transportation Segment Number" | Group-Object -Property "Record Key","Sequence Number")
foreach ($group in $groups) { $group.Group | select -last 1 }
Record Key     Sequence Number Transportation Segment Number Arrival Date
----------     --------------- ----------------------------- ------------
18010312076777 1               2                             01/17/2018
18010312076867 1               2                             01/17/2018
18010312078634 1               2                             01/18/2018
18010912098476 1               4                             1/11/2018
18010912099327 1               2                             01/17/2018
18010912101156 1               2                             01/17/2018

这可能不是最优雅的解决方案，但似乎比目前的问题要复杂，并且确实有效(假设我已经正确理解了问题(。