1 Reply Latest reply: Jan 17, 2018 3:08 PM by Sa Ma RSS

    Displaying top 3 repetitive terms across multiple columns along with the count of occurence

    Sa Ma

      As is evident by the section I have posted in, I am finding my way around Qlik. I have a CSV file in which one "column" has up to 3 names separated by "#". I have used the data manager to separate the values in this column in to 3 separate columns. Now, the same name may occur in any of the 3 columns for multiple "rows". My aim is to find the top 3 names that are occurring most frequently along with the count of occurrence.

       

      IDName 1Name 2Name 3
      1ABC
      2BC-
      3CAB
      4BC-
      5A--

       

      Which formula would work best in this case? Also, is there a way to minimize the creation of these additional columns which have no use beyond this calculation?

        • Re: Displaying top 3 repetitive terms across multiple columns along with the count of occurence
          Sa Ma

          I might have not done it in the most elegant fashion, but managed to find my way around. I first loaded these values in another table using the CrossTable function:

           

          CrossTable(Name Column,Names) LOAD [ID],[Name 1],[Name 2],[Name 3] Resident [table];

           

          Then for display, I finally settled on a bar chart but to display individual names and count, I used the following:

           

          FirstSortedValue(Names,-aggr(count(Names),Names),1)

          count({<Names={"$(=FirstSortedValue(Names,-aggr(count(Names),Names),1))"}>}[Names])

           

          I suppose there must be a better way of doing this, but for now I am glad to have arrived at the intended result.