# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
cancel
Showing results for
Did you mean:
Creator

## Finding invalid combinations of codes - one code without another

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:
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:
resident Codes
where Code = 'X';

LEFT JOIN
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;

1 Solution

Accepted Solutions
Specialist III

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

2 Replies
Specialist III

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

Creator

Interesting - thanks!