aggr firstsortedvalue

[vdcastro]

Hello everyone,

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

I have this dataset:

Ci	O	Pi	En
-	E	P1	E1
-	E	P2	E2
-	E	P8	E3
-	E	P8	E4
-	E	P8	E5
-	E	P2	E6
-	E	P9	E7
C1	C	P1	-
C2	C	P8	-
C3	C	P1	-
C5	C	P8	-

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

[sunny_talwar]

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

[vdcastro]

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

[sunny_talwar]

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?

[vdcastro]

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

[Kushal_Chawda]

do you want to show C2,C5  both ?

[vdcastro]

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

[sunny_talwar · Accepted solution]

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

[Kushal_Chawda]

script solution

Data:

LOAD if(Ci='-',null(),Ci) as Ci, 

     O,

     Pi,

     En

FROM

[https://community.qlik.com/thread/274523]

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

Left Join(Data)

LOAD Pi,

     COunt(En) as CountEn

Resident Data

where O='E'

Group by Pi;

Left Join(Data)

LOAD max(CountEn) as CountEn,

     1 as MaxCountEn

Resident Data;

Left Join(Data)

LOAD Ci,

     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

[sunny_talwar]

Paji tussi great ho.... tofa kabul karo