Filtering Straight table to show a single instance of a dimension

[manasbn]

Hello all, 

I have a straight table like below:

Rank, Member, Group, Therapy, Procedure, Total Amt, Member Amt, Total Amt + Member Amt

1, M1, G1, T1, P1, 10, 20, 30

2, M2, G2, T2, P2, 10,10,20

2, M1, G1, T2, P2, 5,14, 19

2, M1, G1, T3, P3, 10,8,18

3, M3, G3, T2, P2, 10,30,40

3, M4, G1, T5, P2, 15, 15, 30

3, M5, G1, T4, P3, 10,8,18

I want to retain only one row per rank (the row that has highest  Total Amt + Member Amt for that particular rank). So output needs to be:

Rank, Member, Group, Therapy, Procedure, Total Amt, Member Amt, Total Amt + Member Amt

1, M1, G1, T1, P1, 10, 20, 30

2, M2, G2, T2, P2, 10,10,20

3, M3, G3, T2, P2, 10,30,40

Thanks in advance!

[anat]

can you try similar to below:

Welcome:

LOAD * Inline [

ID, Name, Total_TRX

1, A, 10

1, B, 20

2, C, 20

3, D, 50

3, E, 1

];

NoConcatenate

MainTable:

LOAD

          ID,

          MAX(Total_TRX) as Total_TRX

Resident Welcome Group By ID Order By ID;

Left Join

LOAD

     ID,

     Total_TRX,

     Name

Resident Welcome;

Drop Table Welcome;