topic Re: Finding invalid combinations of codes - one code without another in QlikView

Finding invalid combinations of codes - one code without another

nicktodd — Wed, 30 Oct 2019 08:19:08 GMT

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;

Re: Finding invalid combinations of codes - one code without another

Saravanan_Desingh — Tue, 21 Apr 2020 03:18:11 GMT

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 ];

Re: Finding invalid combinations of codes - one code without another

nicktodd — Tue, 21 Apr 2020 12:56:47 GMT

Interesting - thanks!