Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
anni_zhang
New Contributor II

List top n nationalities in a text box

Hi everyone,

I have a problem with listing top n values in a text object.

The scenario is to list top 5 nationalities for student enrollment (count(studentID)).

I tried rank and firstsortedvalue, none of it work. Because firstsortedvalue does not accept nested aggregation so I cannot put count(studentID). Besides, it only provide the top 1 value.

Anybody could help please? Thank you very much in advanced!

Regards,

Anni

Tags (2)
1 Solution

Accepted Solutions
anni_zhang
New Contributor II

Re: List top n nationalities in a text box

Hi Elena,

Thank you very much. This is really helpful. The only problem is that the sorting is in alphabetic order of Nationality. But I need the sorting by the enrollment. I've changed to below formula and it works fine! Thanks a lot again!

Concat(AGGR(IF(Rank(Count(studentID))<=5, NATIONALITY),NATIONALITY),', ', aggr(rank(Count(studentID) ), NATIONALITY))

Regards,

Anni

6 Replies
elena888
Valued Contributor

Re: List top n nationalities in a text box

Hi!

You can try this:

Concat(AGGR(IF(Rank(Count(studentID), 4)<=5, NATIONALITY), NATIONALITY) , ', ')

Let me know!

anni_zhang
New Contributor II

Re: List top n nationalities in a text box

Hi Elena,

Thank you very much. This is really helpful. The only problem is that the sorting is in alphabetic order of Nationality. But I need the sorting by the enrollment. I've changed to below formula and it works fine! Thanks a lot again!

Concat(AGGR(IF(Rank(Count(studentID))<=5, NATIONALITY),NATIONALITY),', ', aggr(rank(Count(studentID) ), NATIONALITY))

Regards,

Anni

vinieme12
Esteemed Contributor II

Re: List top n nationalities in a text box

Hi,

Elena's Expression is correct but it will concatenate in LOAD order and NOT in Order of their ranks.

If you want to concatenate in order of the RANKS, then do the below

//////////////////////////////////////////////////////////

FACT:

LOAD

RowNo() as StudentID,

PICK(Ceil(7*Rand()),'India','UK','Japan','HongKong','Taiwan','Germany','Spain','Turkey','Italy','China') as Nationality

Autogenerate(50);

NoConcatenate

SortOrder:

LOAD

Nationality,

count(StudentID) as SortOrder

RESIDENT FACT

GROUP BY Nationality;

Expression in Textbox

=Concat(AGGR(IF(rank(count(StudentID),1,1)<=5, Nationality),Nationality) , ', ',-SortOrder)

vinieme12
Esteemed Contributor II

Re: List top n nationalities in a text box

if your Query is resolved please close the thread

https://community.qlik.com/docs/DOC-14806

MVP
MVP

Re: List top n nationalities in a text box

concat(aggr(if(rank(count(DISTINCT student), 4)<=4, country), country), ',', -aggr(count(DISTINCT student), country))

anni_zhang
New Contributor II

Re: List top n nationalities in a text box

Dear all,

Thanks all for your answers. Elena's answer was half correct and I figure out the way to sort the nationality by the number of students.

I forgot to mark a correct answer maybe that's why the question is still open.

Thanks Vineeth and maxgro for your answers anyway:)

Kind Regards,

Anni