topic Re: exclude value in QlikView

exclude value

mambi — Thu, 18 Jul 2013 17:43:41 GMT

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 ?

Re: exclude value

Anonymous — Thu, 18 Jul 2013 17:51:09 GMT

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

Re: exclude value

tresB — Thu, 18 Jul 2013 18:02:11 GMT

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;

Re: exclude value

mambi — Thu, 18 Jul 2013 18:03:45 GMT

Don't work !

Re: exclude value

mambi — Thu, 18 Jul 2013 18:05:53 GMT

Work but still have Product1 in the list of ALLProducts

Re: exclude value

Anonymous — Thu, 18 Jul 2013 18:08:00 GMT

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;

Re: exclude value

mambi — Thu, 18 Jul 2013 18:10:27 GMT

Thank's a lot sir

Re: exclude value

Anonymous — Thu, 18 Jul 2013 18:12:16 GMT

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

Re: exclude value

mambi — Thu, 18 Jul 2013 18:12:22 GMT

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

Re: exclude value

tresB — Thu, 18 Jul 2013 18:14:23 GMT

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

Re: exclude value

Anonymous — Thu, 18 Jul 2013 18:17:20 GMT

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

Re: exclude value

mambi — Thu, 18 Jul 2013 18:18:45 GMT

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

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

Re: exclude value

mambi — Thu, 18 Jul 2013 18:20:47 GMT

this time work fine as i expected

Best regards

Re: exclude value

Anonymous — Thu, 18 Jul 2013 18:22:08 GMT

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;

Re: exclude value

mambi — Thu, 18 Jul 2013 18:26:10 GMT

yes, this help me better