topic how to replace count distinct expression with sum? in QlikView

how to replace count distinct expression with sum?

Mon, 09 Feb 2015 10:38:29 GMT

Hi,

please help me in replacing count distinct expression with sum.As count distinct increases the complexity .I need to replace it with sum .

For your reference please find the attached document.

Thanks in Advance.

Sreehari

Re: how to replace count distinct expression with sum?

jagan — Mon, 09 Feb 2015 10:48:13 GMT

Hi,

Use this script

table1:

LOAD * inline [

orderid,amount,month

11,1000,jan

21,1000,jan

31,1000,jan

21,1000,jan

13,1000,jan

14,1000,feb

15,1000,feb

11,1000,feb

13,1000,feb

16,1000,feb

12,1000,feb

14,1000,feb

18,1000,feb

19,1000,feb

12,1000,mar

11,1000,mar

19,1000,mar

17,1000,mar

13,1000,mar

12,1000,mar

15,1000,mar

];

DistinctORder:

LOAD DISTINCT

orderid,

1 AS Flag

RESIDENT table1;

Now use

Sum(Flag) AS expresion to count distinct orders.

Regards,

Jagan.

Re: how to replace count distinct expression with sum?

alexandros17 — Mon, 09 Feb 2015 10:51:52 GMT

Take a look at this

Re: how to replace count distinct expression with sum?

robert_mika — Mon, 09 Feb 2015 11:28:22 GMT

.As count distinct increases the complexity

Just for curiosity -Why?

Re: how to replace count distinct expression with sum?

jagan — Tue, 10 Feb 2015 01:56:54 GMT

Hi Mika,

Count of Distinct doesn't increase the complexity, check below link

A Myth About Count(distinct …)

Regards,

Jagan.

Re: how to replace count distinct expression with sum?

jmmayoral3 — Tue, 10 Feb 2015 15:12:47 GMT

Hello.

Try this script:

// 1.- Load your table. In this example I named it table1

table1:

LOAD * inline [

orderid,amount,month

11,1000,jan

31,1000,jan

21,1000,jan

13,1000,jan

15,1000,feb

11,1000,feb

13,1000,feb

16,1000,feb

12,1000,feb

14,1000,feb

18,1000,feb

19,1000,feb

11,1000,mar

19,1000,mar

17,1000,mar

13,1000,mar

12,1000,mar

15,1000,mar

21,1000,jan

12,1000,mar

14,1000,feb

17,1000,mar

];

// 2.- Reload your table sorted by fields you want unique values. In this example orderid and month.

// At same time, create a new field that indicates if is unique or not. 1 if it is unique, 0 if it is repeated. In this example I named it 'Countdistinct'

// Don´t forget Noconcatenate.

NoConcatenate

Aux:

Load *,

if(AutoNumberHash128(orderid,month)

<> AutoNumberHash128(peek('orderid'),peek('month')),1,0) as CountDistinct

RESIDENT table1

ORDER BY orderid,month;

Drop table table1;

Rename Table Aux to table1;

// 3. Now, you can do a SUM of 'CountDistinct' field.

Note. If you can load your table sorted, you can try use a preceding load and avoid to create an aux table, drop original table and rename aux to original.