Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Partner
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

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

View solution in original post

11 Replies
Highlighted

Re: aggr firstsortedvalue

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

Highlighted
Partner
Partner

Re: aggr firstsortedvalue

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

Highlighted

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
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
MVP

Re: aggr firstsortedvalue

do you want to show C2,C5  both ?

Highlighted
Partner
Partner

Re: aggr firstsortedvalue

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

Highlighted

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

View solution in original post

Highlighted
MVP
MVP

Re: aggr firstsortedvalue

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

Highlighted

Re: aggr firstsortedvalue

Paji tussi great ho.... tofa kabul karo