Finding invalid combinations of codes - one code without another

[nicktodd]

Consider the Codes table below. I want to find cases where there is an 'X' Code for an ID with no matching 'Y' Code (3 and 6 in this example). I can do it by creating a temporary table but I feel there is a more elegant solution that I'm missing.

Any suggestions?

Thanks

Codes:
LOAD * INLINE [
ID, Code
1, X
1, Y
1, X
2, X
2, Y
2, Z
3, X
3, Z
4, X
4, Y
4, Z
5, Y
6, X
7, A
8, B
9, C
];

NoConcatenate

Errors1:
LOAD ID, Code as C1
resident Codes
where Code = 'X';

LEFT JOIN
LOAD ID, Code as C2
resident Codes
Where Code = 'Y';

NoConcatenate

Errors:
LOAD 'X no Y' as Error, ID
resident Errors1
where C1 = 'X' and IsNull(C2);

DROP table Errors1;

[Saravanan_Desingh · Accepted solution]

One solution is:

Codes:
LOAD [ID], If(Index(Concat(DISTINCT Code),'X')>0 And Index(Concat(DISTINCT Code),'Y')=0,'Y') As Flag
Group By [ID]
;
LOAD * INLINE [
ID, Code
1, X
1, Y
1, X
2, X
2, Y
2, Z
3, X
3, Z
4, X
4, Y
4, Z
5, Y
6, X
7, A
8, B
9, C
];

[nicktodd]

Interesting - thanks!