select specific rows

[Sir_12345]

I have this table and I d like to keep for each customer the shop with the highest amount. When the amounts are equal for a shop or for different shops (per customer), I need to keep the shop with the latest date. Could you help me with this?

[krishna_2644]

Is this the required output:

[Sir_12345]

could you provide me the script?

[Kushal_Chawda]

@Sir_12345  try below

Data:
LOAD * Inline [
Customer,Shop, Amount, Date
A,100,500,11/10/2015
A,101,500,09/10/2015
A,101,550,09/11/2015
B,100,600,03/10/2015
B,101,600,05/10/2016
C,100,100,05/10/2016
C,101,50,05/11/2016
C,102,100,05/12/2016
C,101,50,05/19/2016
C,104,100,05/14/2016
C,105,100,05/15/2016
C,106,100,05/13/2016 ];

Final:
LOAD *,
if(Amount=Previous(Amount),Peek('Same_Amount_Group'),RangeSum(Peek('Same_Amount_Group'),1)) as Same_Amount_Group,
if(Amount=Previous(Amount),1,0) as Flag
Resident Data
Order by Customer,Amount,Date;

DROP Table Data;

Left Join(Final)
LOAD Same_Amount_Group,
     date(max(Date)) as Date,
     1 as same_amount_flag
Resident Final
where Flag=1
Group by Same_Amount_Group;

DROP Fields Same_Amount_Group,Flag;

Create a chart with Customer,Shop & Date as dimension and below measure

=sum({<same_amount_flag={1}>}Amount)

[krishna_2644]

Temp:
Load * Inline [
Customer, Shop, Amount, Date
A, 100, 500, 11/10/2015
A, 101, 500, 9/10/2015
B, 100, 600, 3/10/2015
B, 101, 600, 5/10/2016
C, 100, 100, 5/10/2016
C, 101, 50, 5/11/2016
C, 102, 100, 5/12/2016
C, 101, 50, 5/19/2016
C, 104, 100, 5/14/2016
C, 105, 100, 5/15/2016
C, 106, 100, 5/13/2016
];

left join(Temp)
Load Customer,
Max(Amount) as Max_Amount
resident Temp
group by Customer;

NoConcatenate
Data:
Load *,
Num(Date) as Date_Num
resident Temp
where Amount = Max_Amount;
drop table Temp;
Rename table Data to Temp;

left join(Temp)
Load Customer,
Max(Date_Num) as Max_Date_Num
resident Temp
group by Customer;

NoConcatenate
Data:
Load *
resident Temp
where Date_Num = Max_Date_Num;
drop table Temp;
Rename table Data to Temp;

Drop fields
Date_Num,
Max_Amount,
Max_Date_Num
;