Aggr / Max / FirstSorted Problem

[chris1987]

Hi,

I'm really struggling with the following and would appreciate some help.

I have a simple table with the following Info in:

• SalesRep
• QuoteNo
• VersionNo
• LineNo
• Qty
• Status
• LineValue

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:

• SalesRep - The name of the Rep
• QuoteNo - The quote number
• VersionNo - This highest version number on the quote
• NoOfLines - Number of lines on the above quote and version
• TotalQty - Total Quantity of items on the above quote and version
• Status - Status of the quote and version
• TotalLineValue - a total value of all the LineValue on the quote and version

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

[sunny_talwar · Accepted solution]

Check the attached

[chris1987]

Sunny, as always you've got the perfect answer!

Thanks

Chris