topic Re: MinDate per Dimension on script level in App Development

MinDate per Dimension on script level

Bashar — Fri, 15 Mar 2024 13:10:34 GMT

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.

Re: MinDate per Dimension on script level

Clement15 — Fri, 15 Mar 2024 13:41:51 GMT

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

Re: MinDate per Dimension on script level

Bashar — Fri, 15 Mar 2024 13:59:12 GMT

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.

Re: MinDate per Dimension on script level

Clement15 — Fri, 15 Mar 2024 14:14:15 GMT

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

Re: MinDate per Dimension on script level

Nicolae_Alecu — Fri, 15 Mar 2024 14:35:48 GMT

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;