Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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:
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;

1 Solution

Accepted Solutions
Highlighted
Specialist III
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]
;
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
];

commQV05.PNG

View solution in original post

2 Replies
Highlighted
Specialist III
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]
;
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
];

commQV05.PNG

View solution in original post

Highlighted
Creator
Creator

Interesting - thanks!