Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mambi
Contributor 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
mov
Esteemed Contributor III

Re: exclude value

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;

14 Replies
mov
Esteemed Contributor III

Re: exclude value

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

MVP
MVP

Re: exclude value

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
Contributor III

Re: exclude value

Don't work !

mambi
Contributor III

Re: exclude value

Work but still have Product1 in the list of ALLProducts

mov
Esteemed Contributor III

Re: exclude value

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
Contributor III

Re: exclude value

Thank's a lot sir

mov
Esteemed Contributor III

Re: exclude value

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

mambi
Contributor III

Re: exclude value

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

MVP
MVP

Re: exclude value

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);
        

Community Browser