Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Sir_12345
Partner - Contributor
Partner - Contributor

select specific rows

Sir_12345_0-1684860694928.png

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?

Labels (1)
4 Replies
Qrishna
Master
Master

Is this the required output:

Snip.PNG

Sir_12345
Partner - Contributor
Partner - Contributor
Author

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)

 

Screenshot 2023-05-24 001451.png

 

Qrishna
Master
Master

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
;