Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Creator

## Aggr in set anaysis

Hi team,

I have data like this

TA:

[

PID,CID,score

1,1,20

1,2,30

1,3,40

2,1,20

2,2,30

2,5,70

];

I am trying to find a max (CID ) for eqach PID and for that row what is the score.

I am writing following set expression but its just returning score for 2,5,70 row  which is 70.

sum( aggr (sum( {<CID={\$(=Max(CID))}>} score),PID))

Expected output :is sum of below rows.

1,3,40

2,5,70

Request to please respond as early as possible.

Regards,

Prashant

1 Solution

Accepted Solutions

HI,

Find the app attached here with.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
2 Replies

HI,

Find the app attached here with.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Luminary

You can get the max CID in the script and then use a simple set analysis:

```TA:
PID,CID,Score
1,1,20
1,2,30
1,3,40
2,1,20
2,2,30
2,5,70
];

PID,
MAX(CID) AS MaxCID
RESIDENT TA
GROUP BY PID;

Final:
PID,
CID,
Score,
IF(CID=MaxCID,'Yes','No') AS Flag
RESIDENT TA;
DROP TABLE TA;

```

Then in the front end: Sum({<Flag={'Yes'}>}Score) would return 110.

vizmind.eu