Display Top 3 values with comma separated

[rajeshqvd]

Hi,

Need to Display Top 3 values with comma separated Thanks.

INPUT:

ID	Sale
111	454
111	54
111	54
111	21
111	12
111	121
222	1
222	12
222	15
222	121
222	51
333	21
333	212
333	15
333	1541
333	454

Required Output:

ID	Sale
111	454,121,54
222	121,51,15
333	1,541,454,212

[prat1507]

Tab:
Load * Inline [
ID, Sale
111, 454
111, 54
111, 54
111, 21
111, 12
111, 121
222, 1
222, 12
222, 15
222, 121
222, 51
333, 21
333, 212
333, 15
333, 1541
333, 454
];

NoConcatenate Tab2:
LOAD *,
if(ID= Previous(ID), Peek('Rank')+1,1) as Rank
Resident Tab
Order by ID,Sale desc;

NoConcatenate Tab3:
Load ID, Concat(Sale,',') as Sale
Resident Tab2 where Rank<=3
Group by ID
;

[sankarraja]

Tab1:
NoConcatenate
LOAD *,
if(ID= Previous(ID), Peek('Rank')+1,1) as Rank
Resident Tab
Order by ID,Sale desc;
drop table Tab;

Tab2:
NoConcatenate
Load ID,Sale,Rank
Resident Tab1 where Rank<=3;
drop table Tab1;

Tab3:
NoConcatenate
Load ID, Concat(Sale,',') as Sale
Resident Tab2
Group by ID;
drop table Tab2;

[Gysbert_Wassenaar]

Like this:

Result:
LOAD ID, Left(SalesList,Index(SalesList,',',3)-1) as Sale;
LOAD ID, Concat(Sale,',', -Sale) as SalesList GROUP BY ID;
Load DISTINCT* Inline [
ID, Sale
111, 454
111, 54
111, 54
111, 21
111, 12
111, 121
222, 1
222, 12
222, 15
222, 121
222, 51
333, 21
333, 212
333, 15
333, 1541
333, 454
];

[rajeshqvd]

Sorry for wrong post please help to get below output

Input:

ID	Sale
111	blue
111	blue
111	blue
111	red
111	red
111	green
111	green
111	green
111	white
222	blue
222	blue
222	blue
222	red
222	red
222	red
222	red
222	white
222	green
222	white
333	black
333	black
333	black
333	white
333	white
333	green
333	green
333	green
333	red

Required Output:

ID	Sale
111	blue,red,green
222	red,blue,White
333	Black,green ,white

[Gysbert_Wassenaar]

What if there are five unique colors for an ID? What's the Top 3 then? How do you rank your colors? Is blue larger than red or is red larger than blue?

[rajeshqvd]

Based on count group by ID

[Gysbert_Wassenaar · Accepted solution]

See attached example