1 Reply Latest reply: Feb 2, 2018 12:54 PM by Bill Markham RSS

    Count Occurence of all distinct values

    Henry Tobler

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