# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for
Did you mean:
Highlighted
Partner

## aggr firstsortedvalue

Hello everyone,

i am trying to create a graph with the firstsortedvalue with aggr but with no sucess.

I have this dataset:

CiOPiEn
-EP1E1
-EP2E2
-EP8E3
-EP8E4
-EP8E5
-EP2E6
-EP9E7
C1CP1-
C2CP8-
C3CP1-
C5CP8-

i want to know the Ci with max (count(Pi with most En))

C5 - 3

C2 - 3

C3 - 2

C1 - 1

I tried:

=max( aggr( count({<O = {'E'}>} En), Pi )) gives me the number 3. It's correct but i want de Ci instead of the sum value.

=sum(Aggr(FirstSortedValue(count({<O = {'E'}>} En), Pi), Ci))   gives me 0 instead of C5. I dont know where i am failling.

can anyone help me how to get the correct expression?

Greetings

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP

## Re: aggr firstsortedvalue

Here are two options

1) =Concat(Aggr(If(If(O = 'C', Count(TOTAL <Pi> If(O = 'E', En))) = Max(TOTAL Aggr(If(O = 'C', Count(TOTAL <Pi> If(O = 'E', En))), Ci, Pi)), Ci), Ci, Pi), ', ')

This gives C2, C5

2) =FirstSortedValue(DISTINCT Ci, -Aggr(If(O = 'C', Count(TOTAL <Pi> If(O = 'E', En))), Ci, Pi))

Is randomly giving C2

11 Replies
Highlighted
MVP

## Re: aggr firstsortedvalue

Why do you need C5 and not C2 here? the count is equal, isn't it?

Highlighted
Partner

## Re: aggr firstsortedvalue

Yes the count is the same. I just choosed C5 because was the first i saw in the example

Highlighted
MVP

## Re: aggr firstsortedvalue

And just to make sure I understand this, why is C2 = 2 and not 1, just like C5? Both C3 and C1 have Pi = P1 and P1 has a count of 1 En. Is this a mistake or am I missing logic here?

Highlighted
Partner

## Re: aggr firstsortedvalue

C2 = 3 because C2 has Pi = P8 and P8 has 3 En (E3, E4, E5) when O = 'E'.

But i made a mistake too.

C3 = 1

Highlighted
MVP

## Re: aggr firstsortedvalue

do you want to show C2,C5  both ?

Highlighted
Partner

## Re: aggr firstsortedvalue

To be exact, yes, but choose one randomly, it's ok too

Highlighted
MVP

## Re: aggr firstsortedvalue

Here are two options

1) =Concat(Aggr(If(If(O = 'C', Count(TOTAL <Pi> If(O = 'E', En))) = Max(TOTAL Aggr(If(O = 'C', Count(TOTAL <Pi> If(O = 'E', En))), Ci, Pi)), Ci), Ci, Pi), ', ')

This gives C2, C5

2) =FirstSortedValue(DISTINCT Ci, -Aggr(If(O = 'C', Count(TOTAL <Pi> If(O = 'E', En))), Ci, Pi))

Is randomly giving C2

Highlighted
MVP

## Re: aggr firstsortedvalue

script solution

Data:

O,

Pi,

En

FROM

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Data)

COunt(En) as CountEn

Resident Data

where O='E'

Group by Pi;

Left Join(Data)

1 as MaxCountEn

Resident Data;

Left Join(Data)

if(len(trim(Ci))>0,MaxCountEn,0) as MaxCountFlag

Resident Data;

1) =Concat(DISTINCT {<MaxCountFlag={1}>}Ci,',')

This gives C2, C5

2) =subfield(Concat(DISTINCT {<MaxCountFlag={1}>}Ci,','),',',1)

Is  giving C2

Note : Here, I have written this condition if(Ci='-',null(),Ci) as Ci to convert '-' to NULL, but if in your actual data value is NULL then no need to write this condition

Highlighted
MVP

## Re: aggr firstsortedvalue

Paji tussi great ho.... tofa kabul karo