Advanced aggregation in list boxes

[#1]

Hi,

Another exercise for well experienced qlikview users ... I kindly ask your help concerning the issue bellow :

load * inline [

id, kunnr, parnr, description,

1, 1, 10, wrong_desc

2, 1, 20, wrong_desc

3, 1, 30, wrong_desc

4, 1, 40, right_desc

5, 2, 10, wrong_desc

6, 2, 20, right_desc];

With a one-click in a list box, I would like to select id=4 and 6. It corresponds to cases where, for a given kunnr, parnr is maximal.

If one can redirected me to another related post, I would also greatly appreciate

Thanks a lot !

Adrien

[#2]

If you have loaded this script, you could add a new listbox...

There do NOT select a "field" but an expression (is the lowest option in the field list...) on listbox tabpage 'general' dropdownlist 'field'

In that expression box, paste this code

=if(id=4 or id=6,'4 and 6' ,'others')

That will result in a listbox with 2 options

- 4 and 6

- others

You can modify it to your wishes...

(if this is what you want, you could also solve this in the loadscript. The ifstatement can be put there as well)

[whiteline]

Hi.

Why don't you just use a list box with 'description' field ?

[#4]

The example is simple just to be clear enough. What I'm looking for is an expression in listbox that will select only the primary keys (field named 'id' in the example) for which the PARNR for a given KUNNR is maximal.

Something in the spirit of

if(parnr=aggr(max(parnr),kunnr),'1','0').

(But this latter is a non-sense ; I have just given it as an illustration of the idea).

[swuehl · Accepted solution]

Not sure if this is what you are looking for:

=aggr(if(parnr=max(total<kunnr> parnr),'Select max'),kunnr,id)

or maybe

=aggr(if(parnr=max(total<kunnr> parnr),id),kunnr,id)

[#6]

I will try it on monday. Thanks !

Edit : Well, it works !  Herr Wuehl is really impressive ! Now I'm going to take some time to understand why this answer is just working.. One can find as attached a qlikview file showing the example + result.