topic Rank Function in QlikView

Rank Function

manojkulkarni — Fri, 21 Apr 2017 07:57:29 GMT

Hi Friends,

I need to display top 3 text with there number of occurrences along with sorted by alphabets. Below is the input table with serial no & text description. In the expected result Resolved & Reviewed appear 3 times,so it has to be sorted on alphabets to meet the requirement.

what would be the Rank() function or any other way to achieve ?

1	Queried
2	Reviewed
3	Closed
4	Resolved
5	Completed
6	Queried
7	Reviewed
8	Closed
9	Resolved
10	Completed
11	Closed
12	Resolved
13	Closed
14	Queried
15	Reviewed
16	Queried

Expected Out put : Resolved & Reviewed appear 3 times, but it has to be sorted on basis of alphabets.

Queried	4
Resolved	3
Reviewed	3

Re: Rank Function

Fri, 21 Apr 2017 08:03:30 GMT

Try this

load

secondcolumn as second,

count9secondcoloumn) as count

resident tablename

group by secondcolumn;

if I am wrong pls hsare us ur application

Re: Rank Function

vinieme12 — Fri, 21 Apr 2017 08:52:37 GMT

Try

if(rank(count(Type),1,3)<=3,rank(count(Type),1,3))

count({<Type = {"=rank(count(Type),1,1)<=3"}>}Type)

Dual( Count(Type) &' | '& rank(count(Type),1,1)

,count({<Type = {"=rank(count(Type),1,1)<=3"}>}Type)

)

Re: Rank Function

manojkulkarni — Fri, 21 Apr 2017 08:54:11 GMT

Out put is dynamic & needs to be displayed in Text Object. Others field selections attached to the out put result.

Re: Rank Function

vinieme12 — Fri, 21 Apr 2017 08:59:53 GMT

Try below

=Concat(Distinct AGGR(if(rank(count(Type),1,3)<=3,Type & ' = ' & rank(count(Type),1,3)) ,Type) ,'' & chr(10))

Re: Rank Function

manojkulkarni — Fri, 21 Apr 2017 09:00:52 GMT

Vineeth, Not working.

Re: Rank Function

vinieme12 — Fri, 21 Apr 2017 09:03:00 GMT

what exactly does not working mean? an error ? not the expected output??

Re: Rank Function

manojkulkarni — Fri, 21 Apr 2017 09:10:08 GMT

Not expected Out. We need to show below results in 3 text objects

Expected out put is

Queried	4
Resolved	3
Reviewed	3

Re: Rank Function

vinieme12 — Fri, 21 Apr 2017 09:16:02 GMT

what is it showing now?

try below

=Concat(Distinct AGGR(if(rank(count(Type),1,3)<=3,Type & ' = ' & count(Type) ,Type) ,'' & chr(10))