Qlik Community

Qlik Sense App Development

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

htsiemens
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

ID

IDInterests
1Music;Tabletennis
2Music
3Dancing
4Dancing

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 :

Temp:

Load  *  inline [ 

ID , Interests

1 , Music;Tabletennis

2 , Music

3 , Dancing

4 , Dancing

];

Data:

NoConcatenate

Load

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

resident Temp

;

Drop table Temp ;

bearschoice
New Contributor

Re: Count Occurence of all distinct values

Hi,

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?

rscampbell
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