Max stock sequence problem

[#1]

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!

[#2]

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.

[#3]

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.