Count Occurence of all distinct values

[htsiemens]

Hi there!

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

ID

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...

[Anonymous]

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]

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?

[Anonymous]

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?