exclude value

[mambi]

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 ?

[Anonymous]

Or this (banned product is a field "banned"):

BannedProducts:
LOAD id, name as banned 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];

[mambi]

i add drop table BannedProducts and it's work also, thank you

what if i want to keep the BannedProducts table for other purpose.

[mambi]

this time work fine as i expected

Best regards

[Anonymous]

I think this may be useful, you have al products, and a flag for banned product Yes/No:

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

ALLProducts:
NoConcatenate
LOAD
*,
if(exists(id), 'Yes', 'No') as BannedFlag
;
LOAD * INLINE [
     id, name,category
     10, Product1,2
     11, Product2,3
     12, Product3,2
     13, Product4,1
];
DROP TABLE BannedProducts;

[mambi]

yes, this help me better