topic Re: Count Occurence of all distinct values in App Development

Count Occurence of all distinct values

htsiemens — Fri, 02 Feb 2018 17:20:18 GMT

Hi there!

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

ID	Interests
1	Music;Tabletennis
2	Music
3	Dancing
4	Dancing

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:

Interest	Count of Occurrences	Header 3
Dancing	2	since row 3 and 4 contain 'Dancing'
Music	2	since row 1 and 2 contain 'Music'
Tabletennis	1	since row 1 contains 'Tabletennis'

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

Re: Count Occurence of all distinct values

Anonymous — Fri, 02 Feb 2018 17:54:02 GMT

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 ;

Re: Count Occurence of all distinct values

bearschoice — Fri, 13 Jul 2018 06:21:32 GMT

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?

Re: Count Occurence of all distinct values

Anonymous — Sat, 14 Jul 2018 03:09:25 GMT

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?