Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a Purchase Orders Table
PO # | PO Date | Item | Qty |
111 | 12/03/2024 | Item 1 | 5 |
112 | 21/03/2024 | Item 1 | 20 |
113 | 25/03/2024 | Item 1 | 8 |
114 | 18/03/2024 | Item 2 | 6 |
115 | 21/03/2024 | Item 2 | 35 |
116 | 25/03/2024 | Item 2 | 34 |
The goal here is to get the Date & Qty for the Earliest Date after Today per Item.
Currently I'm stuck after doing this
[Purchase Orders]:
LOAD
[Item],
Date(Min([PO Date])) AS [PO Next Delivery Date],
Sum([Qty]) AS [PO Qty]
From TF_PurchaseOrders.qvd
Where [PO Date] > Date(Today())
Group by [Item],[PO Date];
The result would look like this (minus the red row because of PO Date > Date(Today))
At this point, I'm still getting multiple dates per item, but I only care about the upcoming date (Green Rows).
PO # | PO Date | Item | Qty |
111 | 12/03/2024 | Item 1 | 5 |
112 | 21/03/2024 | Item 1 | 20 |
113 | 25/03/2024 | Item 1 | 8 |
114 | 18/03/2024 | Item 2 | 6 |
115 | 21/03/2024 | Item 2 | 35 |
116 | 25/03/2024 | Item 2 | 34 |
Basically what I want to group by the minimum date per item, and my expected result would look like this, and then I left join it to my original table to have this on an item level.
PO # | PO Date | Item | Qty |
112 | 21/03/2024 | Item 1 | 20 |
114 | 18/03/2024 | Item 2 | 6 |
I appreciate any help.
Hello, you can use this code. Normally it meets your expectations.
Hi Clement15,
Thanks for the reply, unfortunately the PO # cant be used like that, I just added it here as a sequential number for simplicity, but its actually a 9 digit code.
Is the Item & PO_Date key unique? If yes this should work
Hello,
I hope this will help
T1:
Load * Inline [
PO#, PO Date, Item, Qty
111, 12/03/2024, Item 1, 5
112, 21/03/2024, Item 1, 20
113, 25/03/2024, Item 1, 8
114, 18/03/2024, Item 2, 6
115, 21/03/2024, Item 2, 35
116, 25/03/2024, Item 2, 34
];
T2:
LOAD
[Item],
Date(Min([PO Date])) AS [PO Next Delivery Date],
Sum([Qty]) AS [PO Qty]
Group by [Item];
Load *
Resident T1
where [PO Date] > today();
Drop table T1;