Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hi,
Please see attached QVW for sample
Regards,
Alex