topic Displaying top 3 repetitive terms across multiple columns along with the count of occurence in Visualization and Usability

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

Anonymous — Wed, 17 Jan 2018 18:16:15 GMT

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.

ID	Name 1	Name 2	Name 3
1	A	B	C
2	B	C	-
3	C	A	B
4	B	C	-
5	A	-	-

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?

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

Anonymous — Wed, 17 Jan 2018 20:06:02 GMT

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.