1 Reply Latest reply: Oct 21, 2011 9:45 AM by Erich Shiino RSS

    How to use sum() instead of count(distinct  )?

    Franziska Schütte

      Hello QlikView-Community,

       

      i want to count the distinct values in a column of a chart.One way, ist to use the function count(distinct column name). Since this function is cost intensive and my real column has 60 Million rows, i want to use the sum function instead. For that i created a new column, e.g. A_Count containing only one distinct value, which is "1". Unfortunatly, i dont know how to sum the column A_Count to get the right distinct value. For better understanding of my question, I created a sample chart below:

       

      In the chart below i have 5 columns. A, B and C are columns with real data. A_Count and B_Count are counter columns. In the columns A,B and C appears  at least one value at least twice.

       

      For example:

      count(distinct A)=6, but sum(A_Count)=10

      count(distinct B)=4, but sum(B_Count)=10

       

      Here is my question how can i use the sum-function in order to get the right distinct values for the columns A or B?

       

      A

      A_Count
      B
      B_CountC
      11111
      21212
      21211
      31114
      41315
      41314
      41317
      5141

      7

      51418
      61219

       

       

       

      Thanks for your help.

      Mila