Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

6 Replies
Anonymous
Not applicable
Author

Hi!

You can try this:

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

Let me know!

Anonymous
Not applicable
Author

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
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

if your Query is resolved please close the thread

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
maxgro
MVP
MVP

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

Anonymous
Not applicable
Author

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