Conditional Count

[FernandaNava]

Hello,

I'm using a (rather large) database in which the main ID can be repeated. I need to establish a condition only for a specific code (let's say, "D"), so that if such ID has any other code besides this one, it is not counted, but if the only time it shows it's with this code, it is counted. 

For example:

ID	Code
1	A
1	B
2	D
3	A
3	D
4	C

For ID 1, I have no problem counting it both for A and B. For ID 2, since its only code is D, it has to be counted. However, for ID 3, since it also has code A, I don't want it to be counted for D  : 

ID	Code
1	A
1	B
2	D
3	A
4	C

This can either be directly when counting the distinct codes or a transformation that gives a table such as the second one.

I appreciate the help.

[Ivan_Bozov · Accepted solution]

Try this script:

TableA:
LOAD
	ID,
	Code,
	IF(MATCH(Code, 'D'), '0', '1') AS Check
INLINE [
ID,Code
1,A
1,B
2,D
3,A
3,D
4,C];

TableB:
LEFT JOIN LOAD 
	ID,
	SUM(Check) AS CheckSum
RESIDENT TableA
GROUP BY ID;

TableC:
NOCONCATENATE LOAD * RESIDENT TableA 
WHERE MATCH(CheckSum, '0') OR NOT MATCH(CheckSum, '0') AND NOT MATCH(Code, 'D');
DROP TABLE TableA; DROP FIELDS Check, CheckSum;

[sunny_talwar]

Is this something you want to change in the script or you want to may be use an expression which counts the ID?

[FernandaNava]

This seems to have done the trick. Thanks a lot!