3 Replies Latest reply: Oct 1, 2012 12:05 AM by ALEXIS TAN RSS

    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