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