Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

New Contributor

Count Occurence of all distinct values

Hi there!

Let us say I have a table with a  string based column 'Interests' holding strings separated by a delimiter



How can I count the occurrence of every distinct interest in my table (without knowing all possible interests before, meaning they are not static, e.g. tomorrow another interest 'chess' might appear in my table)?

I.e. the expression shall give the following result:

InterestCount of OccurrencesHeader 3
Dancing2since row 3 and 4 contain 'Dancing'
Music2since row 1 and 2 contain 'Music'
Tabletennis1since row 1 contains 'Tabletennis'

I read about the SubField  and SubStringCount functions. But I cannot glue it together...

3 Replies

Re: Count Occurence of all distinct values

Maybe like the attached, which uses this load script :


Load  *  inline [ 

ID , Interests

1 , Music;Tabletennis

2 , Music

3 , Dancing

4 , Dancing





SubField(Interests, ';')  as  [Interest]

resident Temp


Drop table Temp ;

New Contributor

Re: Count Occurence of all distinct values


I have a problem I'm trying to solve that extends from this one. In my case, I have been able to show the breakdown of my equivalent of the Count of Occurrences using the Count() function as you have in Count(Interest). What I need to do now is to highlight in a gauge the number of rows of Interests where the count is a particular value, set against the total number of rows in the summary. So in this example, I would like to be able to select Dancing and have the gauge show 2/3 (based on the 2 in the row total, as opposed to the value 'Dancing'). Yes, it's a somewhat crazy requirement, but it's what's needed.

I've tried something like Count({$<[Count(Interest)]={2}>} Interest) to get the rows but this produces a sum of the values selected, not the row count so I guess I'm missing the point here.

Any wise words out there please?

New Contributor III

Re: Count Occurence of all distinct values

In your case, could you:

instead of dropping the table retain it and have the ID be what links it, call Interest to another column (e.g. keyword)

distinct count on interest and distinct count on keywords via Set Analysis and display?

Community Browser