Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm really struggling with the following and would appreciate some help.
I have a simple table with the following Info in:
What I need is a summary table which will show all of the information, but only for the highest quote number. So in the following fields:
So for example, here's the data:
load * inline [
SalesRep,Quote,Version,LineNo,Qty,Status,LineValue,
Chris,00001,1,1,10,C,100,
Chris,00001,1,2,5,C,100,
Chris,00001,1,3,10,C,100,
Chris,00001,2,1,10,I,200,
Chris,00001,2,2,10,I,200,
Chris,00001,2,3,10,I,200,
Chris,00001,3,1,20,O,300,
Chris,00001,3,2,20,O,300,
Chris,00001,3,3,20,O,300,
John,00002,1,1,1,O,500,
John,00003,1,1,5,O,300,
John,00003,2,1,5,O,300,
Bob,00004,1,1,5,O,400,
Bob,00004,2,1,5,O,400,
Bob,00004,3,1,5,I,500
Bob,00004,3,2,25,I,500
];
and I need the output to look like this:
SaleRep | Quote No | Version | NoOfLines | TotalQty | Status | TotalLineValue |
---|---|---|---|---|---|---|
Chris | 00001 | 3 | 3 | 60 | O | 900 |
John | 00002 | 1 | 1 | 1 | O | 500 |
John | 00003 | 2 | 1 | 5 | O | 300 |
Bob | 00004 | 3 | 2 | 30 | I | 1000 |
I've tried using the aggr(), Max() and FirstSortedValue() - which works for the Qty and value but as soon as I drop in the Version Field I get a list of all the versions.
Thanks
Chris
Check the attached
Check the attached
Sunny, as always you've got the perfect answer!
Thanks
Chris