Count distinct based on contents of more than one field

[#1]

Hello again, community.

I reviewed many of the existing posts and got close but no cigar. Need an expression to count distinct member-measure combinations where NO rows have 'N' for ConsolidationFlag AND NO rows have 'GREEN' for ReviewStatus. So for the table below, count would be 1 because MemberName A, MeasureID A does not have any 'Y's for ConsolidationFlag and no ReviewStatus of Green. For MemberName A and MeasureID B, would NOT count b/c one rows has a ReviewStatus of Green. And for MemberName A and Measure C, would NOT count b/c ConsolidationFlag = 'Y' for one or more rows. Make sense? Many thanks in advance!!!!

MemberName	ProviderName	MeasureID	ConsolidationFlag	ReviewStatus
A	A	A	N	YELLOW
A	B	A	N	RED
A	C	A	N	OPEN
A	A	B	N	YELLOW
A	B	B	N	RED
A	C	B	N	GREEN
A	A	C	Y	YELLOW
A	B	C	Y	RED
A	C	C	Y	OPEN

[boorgura]

Something like this should do it.

Test:
LOAD * Inline [
MemberName, ProviderName, MeasureID, ConsolidationFlag, ReviewStatus
A, A, A, N, YELLOW
A, B, A, N, RED
A, C, A, N, OPEN
A, A, B, N, YELLOW
A, B, B, N, RED
A, C, B, N, GREEN
A, A, C, Y, YELLOW
A, B, C, Y, RED
A, C, C, Y, OPEN
];

qualify *;
Temp:
Noconcatenate
Load distinct MemberName & MeasureID as KeyID
resident Test where ConsolidationFlag = 'Y' or ReviewStatus = 'GREEN';

unqualify *;

Final:
Noconcatenate LOAD Distinct MemberName & MeasureID as KeyID,
1 as Cnt
resident Test where not exists (Temp.KeyID, MemberName & MeasureID);

[boorgura]

Please note that I did not drop any tables - which actually can be dropped off.

[#4]

Thanks for this, Rocky. Is it possible to achieve without modifying load? I.e. using and expression and set analysis? Thanks again!

[pover]

If you could somehow give each MemberName, MeasureID combination a unique ID in the script using the autonumber() function it would be alot easier and you could use the following formula which uses implicit set analysis:

count( {$<UniqueID= E({1<ConsolidationFlag={'Y'}>})*E({1<ReviewStatus={'Green'}>})>} distinct UniqueID)

Otherwise, I'm sure it is possible, but it's going to take some more thinking...

[pover]

Ok...try this so you don't have to change the script.

=sum(aggr(count( {$<MeasureID= E({1<ConsolidationFlag={'Y'}>})*E({1<ReviewStatus={'Green'}>})>} distinct MeasureID),MemberName))