Filtering Straight table to show a single instance of a dimension

manasbn — Mon, 31 Jan 2022 08:19:21 GMT

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!

Re: Filtering Straight table to show a single instance of a dimension

anat — Mon, 31 Jan 2022 10:55:52 GMT

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;