topic Re: How to avoid the distinct in QlikView

How to avoid the distinct

Mon, 23 Dec 2013 02:18:44 GMT

Hi All,

I have one table:

YearMonth, MemberId, LoginMinutes

2013-1,123,300

2013-1,123,500

2013-1,125,500

2013-2,123,900

And I want to use one pivot table to show how many unique visitor to login the web site:

But now I use YearMonth as the Dimension and use count(distinct MemberId) as the expression.

However, I heard that use distinct is not efficient, could you suggest which expression should be used to instead of distinct?

Thanks.

Re: How to avoid the distinct

Anonymous — Mon, 23 Dec 2013 02:35:02 GMT

Hi Isaac,

This blog may give you the answer you seek:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/22/a-myth-about-countdistinct

Cheers,

Rod

Re: How to avoid the distinct

Anonymous — Mon, 23 Dec 2013 02:47:03 GMT

Hi Isaac,

Can give some example for expect result?

Regards,

Jerry

Re: How to avoid the distinct

Anonymous — Mon, 23 Dec 2013 03:01:52 GMT

Is the performance of the application slower than desired? If so, you may need to move some of the expression into the script itself. Otherwise the Distinct issue is a myth as Rod pointed out.

Re: How to avoid the distinct

jagan — Mon, 23 Dec 2013 04:21:46 GMT

Hi Isaac,

If you want to avoid DISTINCT then do the following

Data:

LOAD

YearMonth, MemberId, LoginMinutes

FROM SourceData;

Members:

LOAD Distinct MemberId,

1 AS MemberFlag

RESIDENT Data;

Now you can just use

Sum(MemberFlag) to get the distinct count of users.

Regards,

Jagan.

Re: How to avoid the distinct

qlikpahadi07 — Mon, 23 Dec 2013 04:50:40 GMT

Hi,

Personally, You have made correct statement for distinct. I always do the same way never thought of that it is not efficient or need to change.

Re: How to avoid the distinct

Mon, 23 Dec 2013 05:32:03 GMT

hi,

Try this exp...

aggr(NODISTINCT count(DISTINCT MemberId), YearMonth)