Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to filter data
where my Customer Account Code starts with Y3XCH???????? and
does not end with a C, S or H
i.e. Y3XCHABCDEFG will be included, but Y3XCHABCDEFGHC won't be included
Counting those included as 1 and those not included as 0
I am using :
Wildmatch("Customer Account Code",'Y3XCH*') and
Not Match(Right("Customer Account Code",1) = 'C' or
Not Match(Right("Customer Account Code",1) = 'S' or
Not Match(Right("Customer Account Code",1) = 'H'),1,0) as Hub4Filter,
But it is totally omitting all Y3XCH*
Can anyone help please.....
Hi,
you forgot brackets
Not Match(Right("Customer Account Code",1)
and I think you'll replace OR with an and
Because for example if I take "Y3XCHABCDEFGHC" the first condition is OK "YXCH" for the second condition it is enough that one is correct for example it does not end with "S" so "Y3XCHABCDEFGHC" is added while its must not .
try this one
if(Wildmatch([Customer Account Code], 'Y3XCH*')>0 and
(
Wildmatch([Customer Account Code], '*C')=0 and
Wildmatch([Customer Account Code], '*S')=0 and
Wildmatch([Customer Account Code], '*H')=0
)
,1,0) as Hub4Filter,
Hi,
you forgot brackets
Not Match(Right("Customer Account Code",1)
and I think you'll replace OR with an and
Because for example if I take "Y3XCHABCDEFGHC" the first condition is OK "YXCH" for the second condition it is enough that one is correct for example it does not end with "S" so "Y3XCHABCDEFGHC" is added while its must not .
try this one
if(Wildmatch([Customer Account Code], 'Y3XCH*')>0 and
(
Wildmatch([Customer Account Code], '*C')=0 and
Wildmatch([Customer Account Code], '*S')=0 and
Wildmatch([Customer Account Code], '*H')=0
)
,1,0) as Hub4Filter,
Thank you so much, that works.
Wish I'd come on here two days ago !!!