Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vengadeshpalani
Contributor

Top 2 Product for Every Category

Hi all,

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

INPUT

CategoryProductSales
AA15
AA210
AA318
AA42
AA53
BB24
BB12
BB46
BB33
CC34
CC22
CC16

OUTPUT  Top 2 Product based on Every Category

CategoryProductSalesRank
AA3181
AA2102
BB461
BB242
CC161
CC242
1 Solution

Accepted Solutions

Re: Top 2 Product for Every Category

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;

3 Replies

Re: Top 2 Product for Every Category

Script? or front end?

vengadeshpalani
Contributor

Re: Top 2 Product for Every Category

Script

Re: Top 2 Product for Every Category

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;