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

[manoranjan]

Hi,

I have below data

TYPE	MEMBER_ID
A	M1
A	M2
A	M3
B	M1
B	M2
B	M4
B	M5

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

TYPE	TotalCount
A	3
B	4

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.

Type	TotalCount	ExclusiveCount
A	3	1
B	4	2

Please help.

Thanks

Mano

[alexandros17]

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

[#3]

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

[alexandros17]

This is a generalized solution:

AAA:

load * inline

[

TYPE, MEMBER_ID

A,M1

A,M2

A,M3

B,M1

B,M2

B,M4

B,M5

];



Left Join



LOAD

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

[#5]

Hi Mano,

Please find the attached file.

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

[manoranjan]

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

))

[manoranjan]

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

))

[manoranjan]

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

))

[manoranjan]

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

))