Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Displaying top 3 repetitive terms across multiple columns along with the count of occurence

As is evident by the section I have posted in, I am finding my way around Qlik. I have a CSV file in which one "column" has up to 3 names separated by "#". I have used the data manager to separate the values in this column in to 3 separate columns. Now, the same name may occur in any of the 3 columns for multiple "rows". My aim is to find the top 3 names that are occurring most frequently along with the count of occurrence.

IDName 1Name 2Name 3
1ABC
2BC-
3CAB
4BC-
5A--

Which formula would work best in this case? Also, is there a way to minimize the creation of these additional columns which have no use beyond this calculation?

1 Reply
Anonymous
Not applicable
Author

I might have not done it in the most elegant fashion, but managed to find my way around. I first loaded these values in another table using the CrossTable function:

CrossTable(Name Column,Names) LOAD [ID],[Name 1],[Name 2],[Name 3] Resident [table];

Then for display, I finally settled on a bar chart but to display individual names and count, I used the following:

FirstSortedValue(Names,-aggr(count(Names),Names),1)

count({<Names={"$(=FirstSortedValue(Names,-aggr(count(Names),Names),1))"}>}[Names])

I suppose there must be a better way of doing this, but for now I am glad to have arrived at the intended result.