aggr in set analysis

[#1]

H i all

I want to display the rows that has max value grouped by id

Data

Id , dep , val

1 a 1

1 b 2

1 c 3 expected Result

2 d 4 1 c 3

2 e 5 2 f 6

2 f 6 3 I 9

3 g 7

3 h 8

3 I 9

Aggr(max(dal),id) this returns 3,6,9

max( {< val = { 3,6,9 } >} val) This gives the correct result in straight table

but if replace the 3,6,9 with Aggr(max(val),id) then the result is not what is expected

max( {< val = { '=aggr(max(val),id)'} >} val)

1 a 1

2 d 4

3 g 7

Can we do something to the expression to make this work?

[fernandotoledo]

See the example below, you will see that your idea is correct, but there is something missing in your set analisys expression:

http://community.qlik.com/media/p/82604.aspx

Best regards

Fernando

[#3]

Normal 0 false false false EN-US X-NONE X-NONE

Thanks for the reply Fernando D. T

Could you please tell me what's wrong with the following expression

Normal 0 false false false EN-US X-NONE X-NONE

Dimensions are id and dep

expression is = max( {1< val = { '=aggr(max(val),id)' } >} val )

Thanks

Thiru

[spsrk_84]

Hi,

In a straight table add ID dimension and also the below calculated dimension

=

aggr((if(rank(sum(val))='1',dep)),Id,dep)

In the expression just give Sum(Val)...

Just try this i hope it will give required result

Regards,

Ajay

[#5]

Normal 0 false false false EN-US X-NONE X-NONE

Thanks ajay

It can be done by restricting the dimensions or by using if in expression

= if( val = aggr( nodistinct max(val),id) , val )

But Question is

If I type 3, 6, 9 in set expression it works fine

Aggr(max(val),id) returns 3,6,9 then

Why this expression doesn't Work?

= max( {1< val = { '=aggr(max(val),id)' } >} val )

Thanks

Thiru

[mongolu · Accepted solution]

try this

MAX({< val = {"=val=Aggr(nodistinct max(val),Id)"} >} val)

[#7]

Thanks Mihai Petcu

It does not work

[mongolu]

oh. but it does.

QlikView 9 SR 6.

you?

[#9]

Sorry Mihai Petcu

Thank you so much it works in both Qview 9 and 10

Thiru