- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How can I use sum() instead of count(Distinct )
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
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Anjali,
can you please provide more clarity on your question. Distinct obviously cost the query performance.
Sreeni
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
did you read this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 …)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maxgro, I don't want to use count(Distinct) instead I want to use Sum (). How can I use that?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use a record counter at script level and then use sum(recordcounter)...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Balraj, How to use a record counter? Can you explain.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please provide sample data
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »