Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try
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