topic Re: select top row for each dimension based on max in QlikView

select top row for each dimension based on max

Fri, 28 Sep 2012 09:29:44 GMT

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)

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

Re: select top row for each dimension based on max

Anonymous — Fri, 28 Sep 2012 10:32:14 GMT

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

Re: select top row for each dimension based on max

whiteline — Fri, 28 Sep 2012 15:09:31 GMT

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.

Re: select top row for each dimension based on max

Mon, 01 Oct 2012 04:05:46 GMT

Hi,

Please see attached QVW for sample

Regards,

Alex