Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to have the max sequence number of a stock status of the last stock date (last movements) from an article per warehouse. I received following table in QV:
Article_ID | Stock_Date | Stock_Whouse | Stock_Status_ID | Max_SeqNum | Stock_Qty |
138111 | 23/09/2010 | 10 | 1 | 4677 | 6 |
138111 | 23/09/2010 | 10 | 2 | 4680 | 1 |
138111 | 23/09/2010 | 10 | 5 | 4678 | 1 |
138111 | 23/09/2010 | 10 | 5 | 4679 | 0 |
But it should be this:
Article_ID | Stock_Date | Stock_Whouse | Stock_Status_ID | Max_SeqNum | Stock_Qty |
138111 | 23/09/2010 | 10 | 1 | 4677 | 6 |
138111 | 23/09/2010 | 10 | 2 | 4680 | 1 |
138111 | 23/09/2010 | 10 | 5 | 4679 | 0 |
I have a table Stock_Historiek where I've all my stock movements (stored in a QVD). For testing I did a filter on article ID 138111
LOAD
Stock_Whouse,
Article_ID,
Stock_Date,
Stock_Status_ID,
Max(Stock_Status_SeqNum) as Max_SeqNum,
Stock_Qty
RESIDENT Stock_Historiek
where Stock_Status_ID <> peek(Stock_Status_ID)
group BY Stock_Whouse,Article_ID,Stock_Date,Stock_Status_ID,Stock_Qty ;
What should I need to do to make this working? Probably something very basic I should know 🙂
Thanks for helping!
HI,
As I see the problem is due to considering the Qty in group by conditon. Please use the below code to get the expected result:-
LOAD
Stock_Whouse,
Article_ID,
Stock_Date,
Stock_Status_ID,
Max(Stock_Status_SeqNum) as Max_SeqNum,
MAX(Stock_Qty) AS Stock_Qty
RESIDENT Stock_Historiek
group BY Stock_Whouse,Article_ID,Stock_Date,Stock_Status_ID;
So once we take out Stock_Qty from group by clause it will work fine.
I solved it by this script:
noconcatenate Load
MAX(Date(Stock_Date,'DD-MM-YY')) AS MAX_Stock_Date,
Stock_Whouse AS MAX_Stock_Whouse,
Article_ID AS MAX_Article
RESIDENT Stock_Historiek
Group by Stock_Whouse ,Article_ID;
inner join load
Stock_Status_ID as MAX_Stock_Status_ID,
Max(Stock_Status_SeqNum) as MAX_Stock_Status_SeqNum,
Date(Stock_Date,'DD-MM-YY') AS MAX_Stock_Date,
Stock_Whouse AS MAX_Stock_Whouse,
Article_ID AS MAX_Article
RESIDENT Stock_Historiek
group by Stock_Date, Article_ID, Stock_Whouse,Stock_Status_ID;
left join load
Stock_Status_ID as MAX_Stock_Status_ID,
Max(Stock_Status_SeqNum) as MAX_Stock_Status_SeqNum,
Date(Stock_Date,'DD-MM-YY') AS MAX_Stock_Date,
Stock_Whouse AS MAX_Stock_Whouse,
Article_ID AS MAX_Article,
Stock_Qty as MAX_Stock_Qty
RESIDENT Stock_Historiek ;
There should be a more performant way I guess.