Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI,
Find the app attached here with.
Regards,
Kaushik Solanki
HI,
Find the app attached here with.
Regards,
Kaushik Solanki
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.