Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
Hi,
Your syntax is correct.
Just check comparing value is case sensitive or not
match() is case-sensitive
Regards
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')
;
another could be (but logic is different)
Where not match(field1&field2, 'a03', 'a04', 'b03', 'b04')
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.
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
We do not care about the null values
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
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;