Powershell僅在第2列-cge第3列基于第1列的組中顯示行-有解無憂

我有一個這樣的 csv：

MPN,Per_Pallet,Customer_Order,Customer_Order_Date,Backordered_by_Pallet,Reserved_Sum
501,116.82,12055,4/28/2021,3.18,1.02
501,116.82,12421,6/7/2021,2.36,1.02
501,116.82,12424,6/7/2021,3.91,1.02
2243,30,12014,4/26/2021,1.4,1
2243,30,12425,6/7/2021,4.8,1
2243,30,12817,7/21/2021,0.4,1
2243,30,13359,9/29/2021,0.6,1
2435,50.22,12014,4/26/2021,1,2
2435,50.22,13311,9/24/2021,1.14,2
218,40,13236,9/15/2021,3,5
218,40,13382,10/4/2021,3,5
7593,64,12670,7/2/2021,5,5
484,8,12582,6/22/2021,0.38,2
484,8,12798,7/16/2021,1.38,2
484,8,13255,9/18/2021,1,2
484,8,13288,9/22/2021,1,2
5647,87,13304,9/23/2021,0.01,1

我需要按 MPN 列分組，然后首先檢查最早的訂單，看看 Backordered_by_Pallet 是否大于或等于 Reserved_Sum。

如果是 -cge 則只顯示該組的那一行。如果不是，則檢查下一個訂單和第一個訂單是否是并顯示它們等等。直到支持的總數大于 Reserved_Sum

這就是我腦海中的樣子：

look at oldest order first for matching MPN
    if oldest orders Backordered > Reserved Sum
        Then only display oldest order
    Else if oldest order   second oldest order > Reserved Sum
            then display both orders
        Else If Less Than, Add Next Order etc

預期輸出：

MPN,Per_Pallet,Customer_Order,Customer_Order_Date,Backordered_by_Pallet,Reserved_Sum
501,116.82,12055,4/28/2021,3.18,1.02
2243,30,12014,4/26/2021,1.4,1
2435,50.22,13311,9/24/2021,1.14,2
218,40,13236,9/15/2021,3,5
218,40,13382,10/4/2021,3,5
7593,64,12670,7/2/2021,5,5
484,8,12582,6/22/2021,0.38,2
484,8,12798,7/16/2021,1.38,2
484,8,13255,9/18/2021,1,2
5647,87,13304,9/23/2021,0.01,1

我已經得到了不同的作品，但我無法弄清楚如何將它們組合在一起：

如果它更大或不是很容易回傳：

$Magic | ForEach-Object {
        If ($_.Backordered_by_Pallet -cge $_.Reserved_Sum) {$_}
        Else {"Nothing To Order"}
        }

我已經嘗試添加一個組

$Magic | Group-Object MPN | ForEach-Object {
        If ($_.group.Backordered_by_Pallet -cge $_.group.Reserved_Sum) {$_}
        Else {"Nothing_Left_To_Order"}
        }

但這會顯示整個組或什么都不顯示，我不知道如何將它們全部組合起來，更不用說如何在需要時添加前一行的數量了。

我相信我需要為每個都做幾層深，所以我對 MPN 進行分組，為那個 mpn 創建一個陣列，然后為該陣列上的每個創建一個陣列（按最舊排序）（不知道如何將前一行拉到add) 然后只匯出結果，然后回圈移動到下一組，依此類推。

像這樣？我知道這不是真的，我就是想不通

 $Magic_Hash = $Magic_File | Group-Object -Property MPN -AsHashTable | Sort $_.group.Customer_Order_Date
        
        ForEach ($item in $Magic_Hash) {
           If ($item.group.Backordered_by_Pallet -cge $_.group.Reserved_Sum) {$_}
        Elseif ($item.group.Backordered_by_Pallet   $item.group.Backordered_by_Pallett["2nd oldest order"] -cge $_.group.Reserved_Sum) {$_}
        else {"Nothing_Left"}
        } 
        ```
Thank you so much for all your help this community is amazing

uj5u.com熱心網友回復：

第一步是根據MPN列/屬性對記錄進行分組，所以讓我們先使用適當命名的Group-Objectcmdlet進行分組：

$records = @'
MPN,Per_Pallet,Customer_Order,Customer_Order_Date,Backordered_by_Pallet,Reserved_Sum
501,116.82,12055,4/28/2021,3.18,1.02
501,116.82,12421,6/7/2021,2.36,1.02
501,116.82,12424,6/7/2021,3.91,1.02
2243,30,12014,4/26/2021,1.4,1
2243,30,12425,6/7/2021,4.8,1
2243,30,12817,7/21/2021,0.4,1
2243,30,13359,9/29/2021,0.6,1
2435,50.22,12014,4/26/2021,1,2
2435,50.22,13311,9/24/2021,1.14,2
218,40,13236,9/15/2021,3,5
218,40,13382,10/4/2021,3,5
7593,64,12670,7/2/2021,5,5
484,8,12582,6/22/2021,0.38,2
484,8,12798,7/16/2021,1.38,2
484,8,13255,9/18/2021,1,2
484,8,13288,9/22/2021,1,2
5647,87,13304,9/23/2021,0.01,1
'@ |ConvertFrom-Csv

$groups = $records |Group-Object MPN

既然它們都正確分組在一起，我們可以開始遍歷每個組，按日期/訂單號對關聯的記錄進行排序，然后輸出第一個符合條件的記錄：

foreach($group in $groups){
  # sort records by order number
  $recordsInGroup = $group.Group |Sort-Object Customer_Order

  # filter records based on the criteria, output only the first 1
  $recordsInGroup |Where-Object {  $_.Backordered_by_Pallet -ge $_.Reserved_Sum } |Select-Object -First 1
}

在前面$_.Backordered_by_Pallet的Where-Object過濾器將美PowerShell中的值轉換為[double]，確保與正確的數值比較$_.Reserved_Sum

uj5u.com熱心網友回復：

代碼本身非常糟糕，但我相信這是有效的。我添加了評論以或多或少地了解思考程序。

需要注意的一件事是，"Nothing To Order"沒有位置或未定義您希望如何顯示它，因為它是一個string，如果您需要顯示此資訊，則可能必須將其插入其中一個單元格或創建一個新列這個。

@'
MPN,Per_Pallet,Customer_Order,Customer_Order_Date,Backordered_by_Pallet,Reserved_Sum
501,116.82,12055,4/28/2021,3.18,1.02
501,116.82,12421,6/7/2021,2.36,1.02
501,116.82,12424,6/7/2021,3.91,1.02
2243,30,12014,4/26/2021,1.4,1
2243,30,12425,6/7/2021,4.8,1
2243,30,12817,7/21/2021,0.4,1
2243,30,13359,9/29/2021,0.6,1
2435,50.22,12014,4/26/2021,1,2
2435,50.22,13311,9/24/2021,1.14,2
218,40,13236,9/15/2021,3,5
218,40,13382,10/4/2021,3,5
7593,64,12670,7/2/2021,5,5
484,8,12582,6/22/2021,0.38,2
484,8,12798,7/16/2021,1.38,2
484,8,13255,9/18/2021,1,2
484,8,13288,9/22/2021,1,2
5647,87,13304,9/23/2021,0.01,1
'@ |ConvertFrom-Csv |
Group-Object MPN | ForEach-Object {
    
    $skip = $false
    [double]$backorderSum = 0

    # Sort by Customer_Order_Date, oldest will be first in line
    foreach($line in $_.Group | Sort-Object {[datetime]$_.Customer_Order_Date})
    {
        if($skip)
        {
            continue
        }

        # If Backordered_by_Pallet is greater than or equal to Reserved_Sum
        if([double]$line.Backordered_by_Pallet -ge [double]$line.Reserved_Sum)
        {
            # Display this line and skip the rest
            $skip = $true
            $line
        }
        else
        {
            # Display this line
            $line

            # Keep a record of previous Values
            $backorderSum  = $line.Backordered_by_Pallet

            # Until this record is greater than or equal to Reserved_Sum
            if($backorderSum -ge [double]$line.Reserved_Sum)
            {
                # Skip the rest when this condition is met
                $skip = $true
            }
        }
    }
} | FT

輸出

MPN  Per_Pallet Customer_Order Customer_Order_Date Backordered_by_Pallet Reserved_Sum
---  ---------- -------------- ------------------- --------------------- ------------
501  116.82     12055          4/28/2021           3.18                  1.02        
2243 30         12014          4/26/2021           1.4                   1           
2435 50.22      12014          4/26/2021           1                     2           
2435 50.22      13311          9/24/2021           1.14                  2           
218  40         13236          9/15/2021           3                     5           
218  40         13382          10/4/2021           3                     5           
7593 64         12670          7/2/2021            5                     5           
484  8          12582          6/22/2021           0.38                  2           
484  8          12798          7/16/2021           1.38                  2           
484  8          13255          9/18/2021           1                     2           
5647 87         13304          9/23/2021           0.01                  1

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331701.html

標籤：电源外壳文件 if 语句 foreach 群客体

上一篇：AWSLambdaPython讀取所有行但不寫入所有行

下一篇：使用Pandas將.csv檔案轉換為科學記數法