Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Bashar
Contributor III
Contributor III

MinDate per Dimension on script level

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.

Labels (2)
1 Solution

Accepted Solutions
Clement15
Creator III
Creator III

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

Clement15_0-1710512046905.png

 

View solution in original post

4 Replies
Clement15
Creator III
Creator III

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

 

Clement15_0-1710510103367.png

 

Bashar
Contributor III
Contributor III
Author

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

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

Clement15_0-1710512046905.png

 

Nicolae_Alecu
Creator
Creator

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;