topic Re: Advanced aggregation in list boxes in QlikView

Advanced aggregation in list boxes

Fri, 28 Sep 2012 15:09:38 GMT

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

Re: Advanced aggregation in list boxes

Fri, 28 Sep 2012 15:15:57 GMT

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)

Re: Advanced aggregation in list boxes

whiteline — Fri, 28 Sep 2012 15:25:31 GMT

Hi.

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

Re: Advanced aggregation in list boxes

Fri, 28 Sep 2012 16:28:02 GMT

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).

Re: Advanced aggregation in list boxes

swuehl — Fri, 28 Sep 2012 16:50:45 GMT

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)

Re: Advanced aggregation in list boxes

Fri, 28 Sep 2012 16:56:35 GMT

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.