Not Exist and Exist with Multiple Values

Lisa2 — Sat, 16 Nov 2024 19:21:53 GMT

Hello, is there a way that I can combine NOT EXISTS, looking for a value in two columns, and EXISTS all in the same WHERE clause? For example: I need to know, from Table A, which Items A AND Items B do not exist in Table B Items, BUT which Prefix A from Table A exist in Table B Prefix. In other words, from Table A, which Items of A and B DO NOT EXITS and Prefix A does EXISTS in Table B. This is going in the LOAD scriptIf anyone could point me in the right direction. Thanks!

Table A:

Items A	Prefix A	Items B	Prefix B
1653-46	1653	6214-58	6214
1653-56	1653	1856-25	1856
3256-98	3256	8123-73	8123
8523-14	8523	3254-94	3254
5648-87	5648	2145-84	2145
7892-92	7892	1125-44	1125
3152-35	3152	4563-70	4563
7559-78	7559	7823-46	7823
6352-74	6352	5613-43	5613
7325-19	7325	3152-20	3152

Table B:

Items	Prefix
1653-56	1653
2145-84	2145
7559-78	7559
3152-20	3152

Results:

Items A	Prefix A	Items B	Prefix B
1653-46	1653	6214-58	6214
3256-98	3256	8123-73	8123
8523-14	8523	3254-94	3254
7892-92	7892	1125-44	1125
3152-35	3152	4563-70	4563
6352-74	6352	5613-43	5613

Re: Not Exist and Exist with Multiple Values

Taoufiq_Zarra — Thu, 02 Jan 2020 19:54:52 GMT

Hi,

with the logic explaine, in the result the line

3256-98

3256

8123-73

8123

must not exist PrefixA=3256 not exist in the TableB.

So, I propose :

Table_A: load * inline [ ItemsA, PrefixA,ItemsB,PrefixB 1653-46,1653,6214-58,6214 1653-56,1653,1856-25,1856 3256-98,3256,8123-73,8123 8523-14,8523,3254-94,3254 5648-87,5648,2145-84,2145 7892-92,7892,1125-44,1125 3152-35,3152,4563-70,4563 7559-78,7559,7823-46,7823 6352-74,6352,5613-43,5613 7325-19,7325,3152-20,3152 ]; Table_B: load * inline [ Items,Prefix 1653-56,1653 2145-84,2145 7559-78,7559 3152-20,3152 ]; output: NoConcatenate LOAD * Resident Table_A WHERE Not EXISTS(Items,ItemsA) and Not EXISTS(Items,ItemsB) and EXISTS(Prefix,PrefixA) ; DROP table Table_A,Table_B;

and the output :

topic Not Exist and Exist with Multiple Values in QlikView

Not Exist and Exist with Multiple Values

Re: Not Exist and Exist with Multiple Values