Rank Function

[manojkulkarni]

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

[#2]

Try this

load

secondcolumn as second,

count9secondcoloumn) as count

resident  tablename

group by secondcolumn;

if I am wrong pls hsare us ur application

[vinieme12]

Try

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

or

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

or

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

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

)

[manojkulkarni]

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

[vinieme12]

Try below

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

[manojkulkarni]

Vineeth, Not working.

[vinieme12]

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

[manojkulkarni]

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

Expected out put is

Queried	4
Resolved	3
Reviewed	3

[vinieme12]

what is it showing now?

try below

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