how to replace count distinct expression with sum?

[#1]

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

[jagan]

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

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.

[alexandros17]

Take a look at this

[robert_mika]

.As count distinct increases the complexity

Just for curiosity -Why?

[jagan]

Hi Mika,

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

A Myth About Count(distinct …)

Regards,

Jagan.

[jmmayoral3]

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.