Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
anni_zhang
Contributor II
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

1 Solution

Accepted Solutions
anni_zhang
Contributor II
Contributor II
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
elena888
Specialist
Specialist

Hi!

You can try this:

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

Let me know!

anni_zhang
Contributor II
Contributor II
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

vinieme12
Champion II
Champion II

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

if your Query is resolved please close the thread

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

maxgro
MVP
MVP

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

anni_zhang
Contributor II
Contributor II
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