topic Re: How can I use sum() instead of count(Distinct ) in QlikView

How can I use sum() instead of count(Distinct )

Thu, 24 Sep 2015 10:53:13 GMT

Hi all,

It is said that using Sum() instead of count(Distinct ) the performance will increase. But I have duplicate Key values in my data.

So how can I use it. Can anyone help me on this?

Regards,

Anjali Gupta

Re: How can I use sum() instead of count(Distinct )

SreeniJD — Thu, 24 Sep 2015 10:55:50 GMT

Hi Anjali,

can you please provide more clarity on your question. Distinct obviously cost the query performance.

Sreeni

Re: How can I use sum() instead of count(Distinct )

maxgro — Thu, 24 Sep 2015 10:56:00 GMT

did you read this?

A Myth About Count(distinct …)

Re: How can I use sum() instead of count(Distinct )

tresB — Thu, 24 Sep 2015 10:58:54 GMT

Those days are gone. Now QlikView uses multi-threading, and there is no such performance issue with distinct clause. Read: A Myth About Count(distinct …)

Re: How can I use sum() instead of count(Distinct )

Thu, 24 Sep 2015 12:45:03 GMT

Maxgro, I don't want to use count(Distinct) instead I want to use Sum (). How can I use that?

Re: How can I use sum() instead of count(Distinct )

Gabriel — Thu, 24 Sep 2015 12:51:54 GMT

Hi,

Richard Pearce of Qlikcentral also has a blog that might useful to read.

http://qlikcentral.com/2015/08/04/count-or-sum1-which-is-best/

Re: How can I use sum() instead of count(Distinct )

Anonymous — Thu, 24 Sep 2015 12:55:06 GMT

You can use a record counter at script level and then use sum(recordcounter)...

Re: How can I use sum() instead of count(Distinct )

Fri, 25 Sep 2015 08:07:24 GMT

Hi Balraj, How to use a record counter? Can you explain.

Re: How can I use sum() instead of count(Distinct )

sasiparupudi1 — Fri, 25 Sep 2015 08:12:45 GMT

Please provide sample data

Re: How can I use sum() instead of count(Distinct )

Fri, 25 Sep 2015 10:03:04 GMT

Hi Anjali,

in you load script add an extra line of code like this:

1 AS RecordCounter

Then in the frontend you can do a Sum(RecordCounter) to count number of rows. Please beware, if for example you have data with say Orders with multiple Orderlines and you want to count unique orders then this won't work. Instead if you want to count orderlines it wil work. Otherwise you need to split the tabels (Order table and Orderline table).

Hope you get the picture.

Greetz,

Marc

Re: How can I use sum() instead of count(Distinct )

Anonymous — Fri, 25 Sep 2015 10:07:48 GMT

Despite the Myth of Count Distinct, record counters are still very useful and I use them extensively. Well worth checking out the suggestions / links that have been suggested.

Re: How can I use sum() instead of count(Distinct )

simenkg — Fri, 25 Sep 2015 10:09:44 GMT

As others have said; today you can use Count(Distinct) without a performance issue.

If you really want to use SUM instead of count(Distinct) you can do the following:

Fact:

load %Keytobecounted, Facts, numbers, etc from fact.qvd (qvd);

CountingTable:

load distinct %Keytobecounted, 1 as Keycounter resident Fact;

This creates a table with the unique keys and a field you can sum that will equal a count of keys.

now you can use the expression Sum(Keycounter) instead of Count(Distinct %Keytobecounted).

Re: How can I use sum() instead of count(Distinct )

qlikviewwizard — Fri, 25 Sep 2015 10:14:03 GMT

Anjali wrote" It is said that using Sum() instead of count(Distinct ) the performance will increase.".

It is not correct. We can use either one. Count(Distinct) won't reduce the performance.

Read tresco's link.

Re: How can I use sum() instead of count(Distinct )

Anonymous — Fri, 25 Sep 2015 10:33:31 GMT

Like many things this all depends......

Count Distinct used to be single threaded and hence could be slow. Some releases ago it was made multi-threaded which made it faster dependent on how many processor cores you have.

But it will take more processor cycles to execute, albeit striped across available cores.

Processor cycles = Electricity used = Heat produced = Global warming

I know I am being pedantic here, generating electricity produces CO2 and every little helps.