3 Replies Latest reply: Jul 13, 2018 11:09 PM by Robert Campbell RSS

    Count Occurence of all distinct values

    Henry Tobler
    Henry Tobler Feb 2, 2018 12:20 PM

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

      • 120 Views
      • Tags:
        • Re: Count Occurence of all distinct values
          Bill Markham
          Bill Markham Feb 2, 2018 12:54 PM (in response to Henry Tobler)

          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
              Neill Bayley
              Neill Bayley Jul 13, 2018 2:21 AM (in response to Bill Markham )

              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?