Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What rule identifies exclusive memebers? Are always the same for each type? Is it possible to identify in the script?
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
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
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
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
))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
))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
))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
))