select top row for each dimension based on max

[#1]

Hi

something you can do in SQL but can Qlikview handle it within a load script or by set analysis?

Given data like

Person, Type, Amount

Harris, Green,  35

Harris, Red,     87.9

Thompson, Yellow,  1004.5

Thompson,Red, 67.8

Stephens, Yellow ,   67.90

Stephens, White,  104.5

Stephens, Blue,    101.89

You want to return the line for each person with the max(Amount)

eg 

Harris, Red,     87.9

Thompson, Yellow,  1004.5

Stephens, White,  104.5

You can get the line   without the middle type field using a simple Max(amount) expression but how can you get the related type?

Thanks

Message was edited by: pmeacock Thanks.  Yes I have tried using rank and I get all the records but in the correct order.  How can I then only pick out the records with a rank of 1 or at least sort by the rank column so all the 1s are at the top?  I have attached my little test app for reference

[Anonymous]

Hi,

Within the Script you can do a reload with an ORDER BY Amount DESC and then a Peek() function to extract the first row. You may need to do an intermediate load using a GROUP BY clause first though if you have multiple entries per person.

Within the front end expressions, you can use the Rank(Sum(Amount)) functions. This would be the better way to do it as selections that filter out unwanted persons will be taken into account.

Regards,

Jonathan

[whiteline]

Hi.

You could use (in a chart or in script) firstsortedvalue() function with -Amount as a sort-weight to get the corresponding type. Look at help.

[#4]

Hi,

Please see attached QVW for sample

Regards,

Alex