Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mambi
Creator III
Creator III

exclude value

Hi,

i have 2 tables :

ALLProducts:
LOAD * INLINE [
     id, name,category
     10, Product1,2
     11, Product2,3
     12, Product3,2
     13, Product4,1
];


BannedProducts:
LOAD * INLINE [
     id, name,category
     10, Product1,2
];
 

i want to get only the list of products that are nor banned

i tried where not exists but doesn't work , is there any other function ?

any idea ?

1 Solution

Accepted Solutions
Anonymous
Not applicable

It sure works:

BannedProducts:

  LOAD * INLINE [
     id, name,category
     10, Product1,2
];

ALLProducts:
NoConcatenate
LOAD
*
where not exists(id)
;
LOAD * INLINE [
     id, name,category
     10, Product1,2
     11, Product2,3
     12, Product3,2
     13, Product4,1
];

Drop Table BannedProducts;

View solution in original post

14 Replies
Anonymous
Not applicable

First, load the table of the banned products, after that load the table of all products with condition

where not exists(id)

After that, you can drop the table of banned products.

Regards,

Michael

tresesco
MVP
MVP

the script would look something like:

BannedProducts:
LOAD * INLINE [
     id, name,category
     10, Product1,2
];

ALLProducts:
NoConcatenate
LOAD IF(not Exists(id),id) as id, name, category INLINE [
     id, name,category
     10, Product1,2
     11, Product2,3
     12, Product3,2
     13, Product4,1
];

Drop Table BannedProducts;

mambi
Creator III
Creator III
Author

Don't work !

mambi
Creator III
Creator III
Author

Work but still have Product1 in the list of ALLProducts

Anonymous
Not applicable

It sure works:

BannedProducts:

  LOAD * INLINE [
     id, name,category
     10, Product1,2
];

ALLProducts:
NoConcatenate
LOAD
*
where not exists(id)
;
LOAD * INLINE [
     id, name,category
     10, Product1,2
     11, Product2,3
     12, Product3,2
     13, Product4,1
];

Drop Table BannedProducts;

mambi
Creator III
Creator III
Author

Thank's a lot sir

Anonymous
Not applicable

No problem.  You possibly missed something small first time...

mambi
Creator III
Creator III
Author

What's if i would like to keep the BannedProducts to compare categories? reload it again?

tresesco
MVP
MVP

this?

................

ALLProducts:

NoConcatenate

LOAD id, name, category INLINE [

     id, name,category

     10, Product1,2

     11, Product2,3

     12, Product3,2

     13, Product4,1

        

] where not Exists (id);