Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bhupaul87
Contributor
Contributor

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

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

bhupaul87
Contributor
Contributor
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;