Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
];
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
];
Interesting - thanks!