Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

mila_ayuhara
New Contributor III

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

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

Tags (3)
1 Reply
erich_shiino
Honored Contributor

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

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

Community Browser