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: 
Anonymous
Not applicable

How to exclude the multiple field values in the script

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

10 Replies
sunny_talwar

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

PrashantSangle

Hi,

Your syntax is correct.

Just check comparing value is case sensitive or not

match() is case-sensitive

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
maxgro
MVP
MVP

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

;

1.png

maxgro
MVP
MVP

another could be (but logic is different)

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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

Anonymous
Not applicable
Author

We do not care about the null values

sorrakis01
Specialist
Specialist

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

yevgeniy
Creator
Creator

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;