Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

select top row for each dimension based on max

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

Tags (2)
3 Replies
jonbrough
Valued Contributor

Re: select top row for each dimension based on max

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
Honored Contributor II

Re: select top row for each dimension based on max

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.

Not applicable

Re: select top row for each dimension based on max

Hi,

Please see attached QVW for sample

Regards,

Alex

Community Browser