Top 2 Product for Every Category

[vengadeshpalani]

Hi all,

What is the best way to transfer from INPUT to OUTPUT table

INPUT

Category	Product	Sales
A	A1	5
A	A2	10
A	A3	18
A	A4	2
A	A5	3
B	B2	4
B	B1	2
B	B4	6
B	B3	3
C	C3	4
C	C2	2
C	C1	6

OUTPUT  Top 2 Product based on Every Category

Category	Product	Sales	Rank
A	A3	18	1
A	A2	10	2
B	B4	6	1
B	B2	4	2
C	C1	6	1
C	C2	4	2

[sunny_talwar]

Script? or front end?

[vengadeshpalani]

Script

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Category, Product, Sales

    A, A1, 5

    A, A2, 10

    A, A3, 18

    A, A4, 2

    A, A5, 3

    B, B2, 4

    B, B1, 2

    B, B4, 6

    B, B3, 3

    C, C3, 4

    C, C2, 2

    C, C1, 6

];

FinalTable:

LOAD *

Where Match(Rank, 1, 2);

LOAD *,

If(Category = Previous(Category), RangeSum(Peek('Rank'), 1), 1) as Rank

Resident Table

Order By Category, Sales desc;

DROP Table Table;