topic Re: aggr firstsortedvalue in QlikView

aggr firstsortedvalue

vdcastro — Wed, 13 Sep 2017 17:44:04 GMT

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

Re: aggr firstsortedvalue

sunny_talwar — Wed, 13 Sep 2017 18:03:48 GMT

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

Re: aggr firstsortedvalue

vdcastro — Thu, 14 Sep 2017 08:26:03 GMT

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

Re: aggr firstsortedvalue

sunny_talwar — Thu, 14 Sep 2017 08:31:37 GMT

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?

Re: aggr firstsortedvalue

vdcastro — Thu, 14 Sep 2017 08:46:53 GMT

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

Re: aggr firstsortedvalue

Kushal_Chawda — Thu, 14 Sep 2017 08:57:27 GMT

do you want to show C2,C5 both ?

Re: aggr firstsortedvalue

vdcastro — Thu, 14 Sep 2017 09:37:04 GMT

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

Re: aggr firstsortedvalue

sunny_talwar — Thu, 14 Sep 2017 11:26:46 GMT

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

Re: aggr firstsortedvalue

Kushal_Chawda — Thu, 14 Sep 2017 11:54:05 GMT

script solution

Data:

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

Pi,

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

Re: aggr firstsortedvalue

sunny_talwar — Thu, 14 Sep 2017 11:55:49 GMT

Paji tussi great ho.... tofa kabul karo

Re: aggr firstsortedvalue

Kushal_Chawda — Thu, 14 Sep 2017 11:57:52 GMT

hahaha ...tujse kam great hu:)

Re: aggr firstsortedvalue

vdcastro — Thu, 14 Sep 2017 13:34:37 GMT

Thanks a lot stalwar1 and kush141087.

Both solutions work.

However i can't change the script. It must be done with set analysis.

I learned a lot with your answers.

Thank you very much for you help and time.

Greetings

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	-

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	-

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	-