Powershell Display rows only if column 2 -cge column 3 for a group based on column 1

Question

I have a csv like this:

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

I need to group by the MPN column then check the oldest order first to see if Backordered_by_Pallet is greater than or equal to Reserved_Sum.

If it is -cge display only that row for that group. if its not, then check to see if the next order plus the first order is and display both of them and so on. until the backorered total is greater than Reserved_Sum

This is what it looks like in my head:

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

Expected Output:

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

I have gotten different pieces to work, but i cant figure out how to put it all together:

returning if its greater or not is easy enough:

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

and i have tried adding in a group by

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

but that displays the whole group or nothing and im not sure how to combine it all, not to mention how to add the previous rows amount if needed.

I believe i need to do a several layer deep for-each so i group the MPN, make an array for just that one mpn, then a for each on that array (sorted by oldest) (not sure how to pull the previous row to add) then export just the results, then the loop moves on to the next group and so on.

Like this? I know this is not real, i jut cant figure it out

 $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

I think this is a fun exercise but I feel what we are missing is how should the result or output look like. — Santiago Squarzon, Oct 21 '21 at 15:47
The second csv output is how i want the final to look, i should probably mark that better — moore1emu, Oct 21 '21 at 15:57

Mathias R. Jessen · Answer 1 · 2021-10-21T16:01:56.003

1

First step is to group the records based on the MPN column/property, so let's do that first, using the aptly named Group-Object cmdlet:

$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

Now that they're all grouped together correctly, we can start going through each group, sort the associated records by date/order number, and then output the first one that matches the condition:

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
}

The + in front of $_.Backordered_by_Pallet in the Where-Object filter will mae PowerShell convert the value to a [double], ensuring correct numeric comparison with $_.Reserved_Sum

edited Oct 21 '21 at 16:01

answered Oct 21 '21 at 15:48

Mathias R. Jessen

157,619
12
148
206

I copied what you have, but i am not getting any output? – moore1emu Oct 21 '21 at 16:00
1

@moore1emu `$_.Group` should've been `$group.Group`, fixed now :) – Mathias R. Jessen Oct 21 '21 at 16:02
hmm, im only getting three results. the part that is missing is if its less than the reserved sum, i need to pull the next order for that mpn and add it in until it keeps displaying orders until its larger than the reserved_sum – moore1emu Oct 21 '21 at 16:06
i think i need ot use a while loop to do an iteration, but im not familiar with them – moore1emu Oct 21 '21 at 16:15
1

@moore1emu Should the output record have the total sum of `Backordered_by_Pallet` for all grouped records up until then, or should it just output the "breaking point" record? Eg. for MPN `2435`, do you expect the last 2 columns on the output record to be `1.4,2` or `2.4,2`? – Mathias R. Jessen Oct 21 '21 at 16:22
it would output each record and the breaking point record. so it would display all orders up to and including the one that puts the total over reserved sum. I updated the question to Bold the Expected Outcome which what final CSV should look like if that helps. – moore1emu Oct 21 '21 at 16:44
so for MPN 2435 it would display order 12014 with 1,2 and order 13311 with 1.14,2. for 484, it would display the first 3 orders, because combined they are greater than the reserved_sum – moore1emu Oct 21 '21 at 16:46

score 1 · Accepted Answer · answered Oct 21 '21 at 22:47

The code itself is quite awful, but I believe this works. I added comments to understand more or less the thought process.

One thing to note is, "Nothing To Order" has no place or is not defined how you want to display this since, it is a string and if you need to display this information it would probably have to be inserted on one of the cells or create a new column for this.

@'
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

OUTPUT

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

wow, its like your in my brain, can you let me know what this is: [double]$backorderSum is and how does it hold onto the value from the previous runs, does += do that? — moore1emu, Oct 22 '21 at 01:06
the "Nothing Left" was just me returning the records that didnt match in my testing instead of leaving them off completely. so instead of not including the row, I was just going to return a line that so "Nothing Left" in my testing — moore1emu, Oct 22 '21 at 01:10
@moore1emu that's correct, you got it right, `[double]$backorderSum` is just a placeholder variable that would hold all the previous values `Backordered_by_Pallet` whenever we enter the `else {...}` condition, and we do that by adding to the variable with `+=` which is not highly efficient but works fine in this case. Hardcoding the variable as `[double]` is not actually needed in this case, if you remove it it will work just fine. — Santiago Squarzon, Oct 22 '21 at 01:11
I really cant thank you enough, ive been racking my brain on this for the past week. I was just starting to look into while loops and iterating on the sum to do it, but couldn't quite get it. — moore1emu, Oct 22 '21 at 01:17
@moore1emu happy to help :) it was not easy task to code this I can understand why you were having a hard time — Santiago Squarzon, Oct 22 '21 at 01:23
is there a max amount of rows it could match on, example if there are 10 orders that are less than the reserved_sum will it go through all of them or does it max out at 3? I believe its infinite, because its always adding the value per each line, but wanted to check — moore1emu, Oct 22 '21 at 02:31
@moore1emu you can test this yourself by adding a very big number on `Reserved_Sum` and adding as many rows as you wish with the same `MPN`. — Santiago Squarzon, Oct 22 '21 at 02:35
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/238417/discussion-between-moore1emu-and-santiago-squarzon). — moore1emu, Oct 22 '21 at 02:36
looks like it maxes out after 4 rows, do i need ot change the if statements to while loops so that it keeps going until its true? actually i had taken the [double] out and after i put it back in, its works now for all rows. so i guess the [double] is important? — moore1emu, Oct 22 '21 at 02:41
@moore1emu works fine for me either way https://i.imgur.com/ySjBs13.png — Santiago Squarzon, Oct 22 '21 at 02:48

Powershell Display rows only if column 2 -cge column 3 for a group based on column 1

2 Answers2

OUTPUT