topic Re: Aggr function not working in P() element function in QlikView

Aggr function not working in P() element function

Anonymous — Thu, 13 Sep 2018 02:35:50 GMT

Hi,

I have an issue while using P() element function with Aggr combination in QlikView

Here is my expression:

concat(aggr(concat({<[Hold Code]=P({<[Hold Code Priority]={"$(=aggr(max([Hold Code Priority]),[Claim Number]))}>})>}[Hold Code]),[Claim Number]))

I am trying to find the possible values for [Hold Code] using the [Hold Code Priority] field. And in order to populate the values for [Hold Code Priority] , I am taking the it's maximum value grouped by [Claim Number]. I am trying to concat those Hold codes.

Since I am using this in a chart with [Claim Number] as dimension, I did an aggr over [Claim Number] field and finally adding an aggregation function outer concat to get the output.

This expression is working fine in a text object when I select a Claim Number. But when I copy this into a chart expression is not working.

Any help on this would be appreciated.

Thanks,

Srikanth

Re: Aggr function not working in P() element function

Anonymous — Thu, 13 Sep 2018 02:46:31 GMT

In continuation to my above question, when I actually pass in static values in the set for P() function like {6,7,8}, I still get the output in the chart.

I guess my understanding of P() function with aggr combination is not correct.

Thanks,

Srikanth

Re: Aggr function not working in P() element function

tresB — Thu, 13 Sep 2018 05:44:32 GMT

'Set analysis gets evaluated once for a chart and not row-wise'. I have seen, knowing this people try using aggr() to get over the problem. Unfortunately, let me tell you that - here too, you are not going to get set analysis executing row-wise of a chart. It's a wrong perception. To achieve, something similar, you might have to create additional field in the script; usually a composite field.

If you could come up with a sample qvw and explain the expected output out of it, we might give a try helping you out. And yes, at the first look at your expression (without realizing much about your requirement, ), expression seems to be not very optimized.

Re: Aggr function not working in P() element function

Anonymous — Thu, 13 Sep 2018 16:31:35 GMT

Thanks Tresesco!! Let me come up with some sample data for my issue and the output I am expecting.

Yes, in order to make it work, I complicated my expression using aggr() and outside concat() function.

I will provide a sample example soon.

Thanks,

Srikanth

Re: Aggr function not working in P() element function

Anonymous — Sat, 15 Sep 2018 07:09:02 GMT

Hi Tresesco,

Here is some sample I came up with.

CN: Claim Number CLN: Claim Line Number HC: Hold Code HCP: Hold Code Priority

Data:

CN	CLN	HC	HCP
12345	123451	A	9
12345	123452	J	8
12345	123453	R	9
12345	123454	F	7
23456	234561	M	5
23456	234562	D	4
23456	234563	D	4
23456	234564	F	6
23456	234565	Q	2
34567	345671	J	8
34567	345672	P	8
34567	345673	F	7
45678	456781	B	5
45678	456782	B	5
45678	456783	V	3
45678	456784	H	2

Output:

CN	HC
12345	A,R
23456	F
34567	J,P
45678	B

I am trying to calculate the Maximum HCP for each CN and concat all HC's for that CN based on highest HCP.

CN 12345 has four CLN's ie., 123451,123452,123453,123454.

Maximum HCP is 9. So concat HC's A and R.

Hopefully I am clear with my sample example.

Thanks,

Srikanth

Re: Aggr function not working in P() element function

swuehl — Sat, 15 Sep 2018 08:48:37 GMT

You can get your result using for example

CN	Concat(DISTINCt Aggr( If(HCP = Max(Total<CN> HCP),HC), CN, CLN),', ')
	A, B, F, J, P, R
12345	A, R
23456	F
34567	J, P
45678	B

You could also flag your CLN with the highest priority in the script, adding after your Data table load

LEFT JOIN (Data)

LOAD CN, Max(HCP) as HCP, 1 as MaxHCP

Resident Data

GROUP BY CN;

and then you can use set analysis to filter your HC per CN:

CN	Concat({<MaxHCP = {1}>} DISTINCT HC,', ')
	A, B, F, J, P, R
12345	A, R
23456	F
34567	J, P
45678	B

Re: Aggr function not working in P() element function

Anonymous — Sun, 16 Sep 2018 16:57:14 GMT

Hi Stefan,

Thank you so much!!

It worked.

Srikanth