Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
'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.
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
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
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 |
Hi Stefan,
Thank you so much!!
It worked.
Srikanth