Aggr in set anaysis

[prashantsanchet]

Hi team,

I have data like this

TA:

LOAD * inline

[

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

[kaushiknsolanki · Accepted solution]

HI,

Find the app attached here with.

Regards,

Kaushik Solanki

[Ivan_Bozov]

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

TA:
LOAD * INLINE [
PID,CID,Score
1,1,20
1,2,30
1,3,40
2,1,20
2,2,30
2,5,70
];

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

Final:
LOAD
     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.