Aggr / Max / FirstSorted Problem

chris1987 — Tue, 27 Feb 2018 15:54:56 GMT

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

Re: Aggr / Max / FirstSorted Problem

sunny_talwar — Tue, 27 Feb 2018 16:18:35 GMT

Check the attached

Re: Aggr / Max / FirstSorted Problem

chris1987 — Tue, 27 Feb 2018 16:30:08 GMT

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

Thanks

Chris

topic Re: Aggr / Max / FirstSorted Problem in QlikView

Aggr / Max / FirstSorted Problem

Re: Aggr / Max / FirstSorted Problem

Re: Aggr / Max / FirstSorted Problem