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

[#1]

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

[swuehl · Accepted solution]

Create a straight table chart with dimension ItemNo.

Then two expressions:

=max(Qty)

and

= FirstSortedValue(Distinct Date, -Qty)

I added a distinct qualifier, so you get a date back when you have two dates with same Qty for the same ItemNo.

edit: I missed that you may have several entries per date and ItemNo and you need to aggregate.

If you don't want to do that in the script, you can try:

=Max(aggr(Sum(Qty),ItemNo,Date))

and

=FirstSortedValue( aggr(Date,ItemNo,Date), aggr(-sum(Qty),ItemNo,Date) )

[Gysbert_Wassenaar]

Try:

Result:

Load ItemNo, max(Qty) as Max, firstsortedvalue(Date, -Qty) as MaxDate

resident Data

group by ItemNo;

[#4]

Jawoll! Awesome, thank you!