Rank function in data load edition

[SK28]

Hi,

I have data as shown below

org	desig	cnt of tic
TCS	A1	123
TCS	A2	122
TCS	A3	100
TCS	a4	95
TCS	A5	90
TCS	A6	85
TCS	a7	80
CTS	B1	123
CTS	B2	122
CTS	B3	100
CTS	B4	95
CTS	B5	90
CTS	B1	80
INF	C1	95
INF	C3	90
INF	C2	80
INF	C4	70

the required output is from each Org, I need Desig based on top 3 count of tickets, in data load editor

Required ouput

org	desig	cnt of tic
TCS	A1	123
TCS	A2	122
TCS	A3	100

CTS	B1	123
CTS	B2	122
CTS	B3	100

INF	C1	95
INF	C3	90
INF	C2	80

or rank function in data load editor

org	desig	cnt of tic	required out
TCS	A1	123	1
TCS	A2	122	2
TCS	A3	100	3
CTS	B1	123	1
CTS	B2	122	2
CTS	B3	100	3
INF	C1	95	1
INF	C3	90	2
INF	C2	80	3

Can you please help me on this

Appreciate your help

[maxgro]

try

T:

LOAD org, 

     desig, 

     [cnt of tic]

FROM

[https://community.qlik.com/t5/New-to-Qlik-Sense/Rank-function-in-data-load-edition/td-p/2144521]

(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

T1:

NOCONCATENATE 

LOAD

*

WHERE

org_num <= 3

;

LOAD 

*, 

if(Peek('org')<>org, 1, Peek('org_num')+1) as org_num 

Resident T

ORDER BY org, [cnt of tic] desc;

DROP Table T;

[rwunderlich]

If you are using Qlik Cloud (or soon QSEoW) you can use the new Window() function.

Data:
Load *
where Rank <= 3
;
Load *,
Window(WRank(4,1), org, 'desc', [cnt of tic]) as Rank
Inline [
org,desig,cnt of tic
TCS,a4,95
TCS,A5,90
TCS,A6,85
TCS,a7,80
TCS,A1,123
TCS,A2,122
TCS,A3,100
CTS,B1,123
CTS,B2,122
CTS,B3,100
CTS,B4,95
CTS,B5,90
CTS,B1,80
INF,C1,95
INF,C3,90
INF,C2,80
INF,C4,70
];

If you don't have Window() function available, here's another flavor of the solution suggested by @maxgro 

Data:

Load *

Inline [

org,desig,cnt of tic

TCS,a4,95

TCS,A5,90

TCS,A6,85

TCS,a7,80

TCS,A1,123

TCS,A2,122

TCS,A3,100

CTS,B1,123

CTS,B2,122

CTS,B3,100

CTS,B4,95

CTS,B5,90

CTS,B1,80

INF,C1,95

INF,C3,90

INF,C2,80

INF,C4,70

];

Inner Join(Data)

Load *

where Rank <= 3

;

Load *,

AutoNumber([cnt of tic], org) as Rank

Resident Data

Order By [cnt of tic] desc

;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com