8 Replies Latest reply: Mar 27, 2013 9:41 AM by Manoranjan Ayyaswamy

# How to Count only the exclusive items in a given aggregated set ?

Hi,

I have below data

TYPEMEMBER_ID
AM1
AM2
AM3
BM1
BM2
BM4
BM5

When I aggregate based on Type, and Count the members I get below result:

TYPE      TotalCount
A3
B4

But, what if I need to Count exclusive members in each Type.

Please note M3 is exclusive only to type A, and M4 & M5 exclusive only to type B. So I want the below result in my Straight Table.

TypeTotalCountExclusiveCount
A31
B42

Thanks

Mano

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

What rule identifies exclusive memebers? Are always the same for each type? Is it possible to identify in the script?

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

HI,

i don't know if it's work but you should try an expression like this:

SUM(IF(TYPE='A', IF(MEMBER_ID='M1',1,0), IF(MEMBER_ID='M4' OR MEMBER_ID='M5' ,1,0)))

I the script would be easier. Add a column like this

IF(TYPE='A', IF(MEMBER_ID='M1',1,0), IF(MEMBER_ID='M4' OR MEMBER_ID='M5' ,1,0)) AS F_ExclusiveMember

and then in the formula

COUNT( F_ExclusiveMember)

Let me know if you try

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

This is a generalized solution:

AAA:

[

TYPE, MEMBER_ID

A,M1

A,M2

A,M3

B,M1

B,M2

B,M4

B,M5

]
;

Left Join

MEMBER_ID,

count(MEMBER_ID) as single

Resident AAA

Group by MEMBER_ID;

the expression tyou want is:

count

({\$ <single={1}>} MEMBER_ID)

Hope it helps

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

Hi Mano,

I have made another field that counts the MEMBER_ID values. For unique values, the total count will be 1. Hence, you would need to count the total values that are '1'.

Hope it helps.

Regards,

Kesh

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

Not sure whether that is correct solution when you have 50+ millions of records. I have got answer from another post in the community. It works, though it takes few seconds to calculate.

=sum(aggr(if(count({<TYPE>} distinct TYPE)=1,1), MEMBER_ID

))

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

Not sure whether that is correct solution when you have 50+ millions of records. I have got answer from another post in the community. It works, though it takes few seconds to calculate.

=sum(aggr(if(count({<TYPE>} distinct TYPE)=1,1), MEMBER_ID

))

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

Not sure whether that is correct solution when you have 50+ millions of records. I have got answer from another post in the community. It works, though it takes few seconds to calculate.

sum(aggr(if(count({<TYPE>} distinct TYPE)=1,1), MEMBER_ID

))

• ###### Re: How to Count only the exclusive items in a given aggregated set ?

Not sure whether that is correct solution when you have 50+ millions of records. I have got answer from another post in the community. It works, though it takes few seconds to calculate.

sum(aggr(if(count({<TYPE>} distinct TYPE)=1,1), MEMBER_ID

))