3 Replies Latest reply: Sep 13, 2013 12:41 PM by Andreas Pesak RSS

    How to get a row value when I use Max() ?

      Hello everybody!

       

      I'm really new to QlikView, amazed so far, but having now a problem, for which I can't find a solution by myself and the forum (maybe I don't know the right keywords...)

       

      Let's say I have a inline table:

       

      Data:

      LOAD * INLINE [

      ItemNo, Qty, Date

      44444, 100, 24.01.2013

      55555, 300, 24.01.2013

      77777, 200, 24.01.2013

      55555, 200, 25.01.2013

      66666, 500, 25.01.2013

      77777, 300, 25.01.2013

      ];

       

      Now I want to get the highest quantity and the corresponding date by ItemNo.

      So the goal is getting this (in a straight table):

       

      ItemNo, Max, Date

      44444, 100, 24.01.2013

      55555, 300, 24.01.2013

      66666, 500, 25.01.2013

      77777, 300, 25.01.2013

       

      For Max I'm using the expression =Max(aggr(Sum(Qty),ItemNo,Date)) (sum up if I have more than one entry a day)

      But how do I get the corresponding date now? Any ideas?

       

      I'd prefer a solution not done in the load script, because I think it should be able to do something like this by the end user themselves But if it's the only way, then say it...

       

      Thank you very much for your help and idea!

       

      Best regards and have a nice weekend

      Andreas

       

      PS: I have only the personal edition, so a written idea/solution would be appreciated