Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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