topic Re: Display Top 3 values with comma separated in Visualization and Usability

Display Top 3 values with comma separated

rajeshqvd — Mon, 12 Apr 2021 19:06:54 GMT

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

Re: Display Top 3 values with comma separated

prat1507 — Wed, 16 Jan 2019 06:18:10 GMT

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
;

Re: Display Top 3 values with comma separated

sankarraja — Wed, 16 Jan 2019 07:16:23 GMT

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;

Re: Display Top 3 values with comma separated

Gysbert_Wassenaar — Wed, 16 Jan 2019 07:38:33 GMT

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
];

Re: Display Top 3 values with comma separated

rajeshqvd — Wed, 16 Jan 2019 07:44:28 GMT

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

Re: Display Top 3 values with comma separated

Gysbert_Wassenaar — Wed, 16 Jan 2019 08:10:57 GMT

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?

Re: Display Top 3 values with comma separated

rajeshqvd — Wed, 16 Jan 2019 08:19:20 GMT

Based on count group by ID

Re: Display Top 3 values with comma separated

Gysbert_Wassenaar — Wed, 16 Jan 2019 09:35:16 GMT

See attached example