Hi Ankit,
so I understand you need the Amt twice - once the individual amound (by which the records are to be filtered and by which you want to single out the top5) and once the total rgd. one particular Party_Name - correct?
Well, I would recommend doing this in two steps although you could possily do it in one - it just makes it a bit easier to understand for others who might end up having to work with your code:
1) Generate a field for the total_amt in the table (aggregate the table in a RESIDENT LOAD using the sum() function
- don't forget the GROUP BY clause - actually, for this aggregated RESIDENT LOAD you need only two fields,
the Party_Name and the individual_amount (which you sum up)
2) Load RESIDENT from the primary table, again aggregating and using the max() function - you will need five lines
with the max() function, each with a different parameter - max(amt, 1), max(amt, 2) etc.
3) To this table (which should have only 5 lines), you join the total_amt, using the Party_Name as the Join_field
It might be done otherwise and you could possibly do it all in fewer steps, but this would be my approach.
HTH
Best regards,
DataNibbler
