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