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_Count | C |
---|---|---|---|---|
1 | 1 | 1 | 1 | 1 |
2 | 1 | 2 | 1 | 2 |
2 | 1 | 2 | 1 | 1 |
3 | 1 | 1 | 1 | 4 |
4 | 1 | 3 | 1 | 5 |
4 | 1 | 3 | 1 | 4 |
4 | 1 | 3 | 1 | 7 |
5 | 1 | 4 | 1 | 7 |
5 | 1 | 4 | 1 | 8 |
6 | 1 | 2 | 1 | 9 |
Thanks for your help.
Mila
Hi, Mila
Let us say that you have a Fact table with the 60 M rows.
Then, at the end of the script, you can create an auxiliary table like this:
AUX_A:
load distinct A, 1 as A_Count
resident Fact;
QlikView will associate the field A since they have the same names.
Then you can use sum( A_Count) to reproduce the count(distinct A)
Hope this helps,
Erich