Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Script? or front end?
Script
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;