Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
vdcastro
Partner - Contributor III
Partner - Contributor III

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

11 Replies
sunny_talwar

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

vdcastro
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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

sunny_talwar

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