Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

g33ksama
New Contributor

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
g33ksama
New Contributor

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

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.

Community Browser