Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HELLO
HOW TO HAVE THE TOP TWO OF CUSTOMERS WHO HAVE MAX NUMBER OF SALES IN SCRIPT
GROUP | CUSTOMER | SALES µ |
A | 100 | 1 |
A | 101 | 4 |
A | 102 | 5 |
A | 103 | 6 |
B | 104 | 10 |
B | 105 | 100 |
B | 106 | 20 |
B | 107 | 30 |
C | 108 | 120 |
C | 109 | 160 |
C | 110 | 140 |
C | 111 | 120 |
C | 112 | 200 |
GROUP | CUSTOMER | SALES µ |
A | 103 | 6 |
A | 102 | 5 |
B | 105 | 100 |
B | 107 | 30 |
C | 112 | 200 |
C | 109 | 160 |
Key in ranking in script is to use sorting, i.e. using order by clause. Try something like:
Input:
LOAD GROUP,
CUSTOMER,
[SALES µ]
FROM
[https://community.qlik.com/t5/New-to-QlikView/TOP-MAX-2-CUSTOMER/td-p/1695653]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1) where RowNo()<13;
Sort:
Load GROUP,
CUSTOMER,
Sum([SALES µ]) as [SALES µ],
AutoNumber(RowNo(),GROUP) as Count
Resident Input
Group By GROUP, CUSTOMER
Order By GROUP, [SALES µ] desc;
NoConcatenate
Output:
Load
*
Resident Sort where Count<3;
Drop Table Sort, Input;
Key in ranking in script is to use sorting, i.e. using order by clause. Try something like:
Input:
LOAD GROUP,
CUSTOMER,
[SALES µ]
FROM
[https://community.qlik.com/t5/New-to-QlikView/TOP-MAX-2-CUSTOMER/td-p/1695653]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1) where RowNo()<13;
Sort:
Load GROUP,
CUSTOMER,
Sum([SALES µ]) as [SALES µ],
AutoNumber(RowNo(),GROUP) as Count
Resident Input
Group By GROUP, CUSTOMER
Order By GROUP, [SALES µ] desc;
NoConcatenate
Output:
Load
*
Resident Sort where Count<3;
Drop Table Sort, Input;