topic Re: How to exclude the multiple field values in the script in QlikView

How to exclude the multiple field values in the script

Anonymous — Thu, 31 Dec 2015 11:10:50 GMT

Dear All,

i am trying to exclude the multiple field values in the script by using like

Load *

from (qvd)

where not match( [Field1], 'a','b')

and not mtach( [Filed2],03, 04)

But is not giving the expected no.of recprds .

could you please help me on this to exclude the multiple field values in the script.

Thanks in advance.

Bhupal

Re: How to exclude the multiple field values in the script

sunny_talwar — Thu, 31 Dec 2015 11:17:24 GMT

Is it giving you more or less rows than you expected? If its giving more may be you need or between them because in its current form, I think it would also include rows like these:

Field1 Field2

c, 03

b, 10

Update: to exclude the above two rows, may be this is what you need:

Where not Match([Field1], 'a','b') or not Match([Field2], '03', '04')

Re: How to exclude the multiple field values in the script

PrashantSangle — Thu, 31 Dec 2015 11:19:23 GMT

Hi,

Your syntax is correct.

Just check comparing value is case sensitive or not

match() is case-sensitive

Regards

Re: How to exclude the multiple field values in the script

maxgro — Thu, 31 Dec 2015 11:19:26 GMT

load * inline [

field1, field2

a,01

b,01

c,01

d,01

a,03

b,03

c,03

d,03

a,04

b,04

c,04

d,04

]

Where not match(field1, 'a', 'b') and not match(field2, '03', '04')

;

Re: How to exclude the multiple field values in the script

maxgro — Thu, 31 Dec 2015 11:24:20 GMT

another could be (but logic is different)

Where not match(field1&field2, 'a03', 'a04', 'b03', 'b04')

Re: How to exclude the multiple field values in the script

Anonymous — Thu, 31 Dec 2015 11:27:58 GMT

It is giving less records than expected.

I will give you few feilds with exluded values in the script.

not match[Customer group 5_CUST_GRP5./BIC/AZSDBBB0100],'03')
and not wildmatch([Inter Company/3rd pt_/BIC/C00056./BIC/AZSDBBB0100],'2')
and not wildmatch([Material_MATERIAL./BIC/AZSDBBB0100],'TAX','TAXABLE FREIGHT','NONTAXABLE FREIGHT','CUSTOMER STOCK','CANADIAN GST','CANADIAN HST','CANADIAN PST')
and not wildmatch([Billing block_BILBLK_ITM./BIC/AZSDBBB0100],'99')

please let me know, if am not correct.

Re: How to exclude the multiple field values in the script

sunny_talwar — Thu, 31 Dec 2015 11:52:21 GMT

Are there Nulls in any of the fields that you are using in your where not match statement? If there are Nulls, they might also be getting excluded

Re: How to exclude the multiple field values in the script

Anonymous — Thu, 31 Dec 2015 11:54:41 GMT

We do not care about the null values

Re: How to exclude the multiple field values in the script

sorrakis01 — Thu, 31 Dec 2015 12:23:00 GMT

Hi Max,

I was reading the discussion and i have a doubt in your answer.

Why in this case don't show all the possible combinations? I explain...

Field1= 'a','b' AND Field2 = '03','04' (Normally will be operator OR) (In my mind the results will be all the combinations except 'a03, a04,b03,b04' but are not)

Why doesn't returns 'a01', 'b01', 'c01', 'd01', 'c03', 'd03', 'a04', 'b04', 'c04', 'd04' it's a property of not match?

I'm confuse in this case

Regards and Happy New Year

Re: How to exclude the multiple field values in the script

yevgeniy — Thu, 31 Dec 2015 12:45:55 GMT

Hi,

As variant

var:

load * inline

[

A, B

a, 03

b, 04

];

Load your script

Where not exists(Field1,'A') and not exists(Field2,'B');

drop table var;

Re: How to exclude the multiple field values in the script

maxgro — Thu, 31 Dec 2015 13:16:52 GMT

I comment the where in my script and add some column;

you can still see the result of Where not match(field1, 'a', 'b') and not match(field2, '03', '04') in the 5th column, the rows with the red rectangle.

not match(field1, 'a', 'b') --> you get the rows with c d (green rectangle, 3rd col)

not match(field2, '03', '04') --> you get the rows with 01 (green rectangle, 4th col)

not match (field1, 'a', 'b') and not match (field2, '03', '04') (red rectangle, 5th col)

The 6th column, flag2, is the result with

Where not match(field1&field2, 'a03', 'a04', 'b03', 'b04')

SCRIPT

load *,

not match(field1, 'a', 'b') as notmatch_field1_ab,

not match(field2, 03, 04) as notmatch_field2_0304,

not match(field1, 'a', 'b') and not match(field2, 03, 04) as notmatch_field1_ab_and_not_match_field2_0304,

not match(field1&field2, 'a03', 'a04', 'b03', 'b04') as flag2

inline [

field1, field2

a,01

b,01

c,01

d,01

a,03

b,03

c,03

d,03

a,04

b,04

c,04

d,04

]

//Where not match(field1, 'a', 'b') and not match(field2, 03, 04)

//Where not match(field1&field2, 'a03', 'a04', 'b03', 'b04') //and not match(field2, 03, 04)

;