Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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) )
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) )
Try:
Result:
Load ItemNo, max(Qty) as Max, firstsortedvalue(Date, -Qty) as MaxDate
resident Data
group by ItemNo;
Jawoll! Awesome, thank you!