MinDate per Dimension on script level

[Bashar]

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.

[Clement15]

Hello, you can use this code. Normally it meets your expectations.

[Bashar]

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.

[Clement15 · Accepted solution]

Is the Item & PO_Date key unique? If yes this should work

[Nicolae_Alecu]

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;