Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chris1987
Creator
Creator

Aggr / Max / FirstSorted Problem

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:

SaleRepQuote NoVersionNoOfLinesTotalQtyStatusTotalLineValue
Chris000013360O900
John00002111O500
John00003215O300
Bob000043230I1000

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

1 Solution

Accepted Solutions
sunny_talwar

Check the attached

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Check the attached

Capture.PNG

chris1987
Creator
Creator
Author

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

Thanks

Chris