if statement equivalent of SQL not like

[#1]

In SQL , I have something like

ABC_table:

Load

TYPE_NM,*;

SQL select * from ABC_table where  TYPE_NM NOT LIKE '%_CUSTOMER'.

In script if statement like works but 'not like'

what is the way to implement the below?

if ( TYPE_NM NOT LIKE '*_CUST OMER', value) as filtered_value,

Thanks.

[JonnyPoole]

Something like:

if(  NOT (   wildmatch5(TYPE_NM , '*_CUSTOMER' )) , value)

(note that i removed a space in CUSTOMER.

[JonnyPoole]

typo:

if(  NOT (   wildmatch(TYPE_NM , '*_CUSTOMER' )) , value)

[#4]

Hi Varum, try this:

if(  NOT (  wildmatch(TYPE_NM , '*_CUSTOMER' )) , 1 , 0)

The meaning is, if in the field TYPE_NM is not '*_CUSTOMER' string the value will be 1 in other way 0.

I hope that it help you!

Regards, Agustin

[maxgro]

do you really need to remove the filter from sql?

ABC_table:

Load

     TYPE_NM,

     *

     ;

SQL select * from ABC_table

where  TYPE_NM NOT LIKE '%_CUSTOMER';

if yes try with

ABC_table:

Load

     TYPE_NM,

     *

Where

     not   wildmatch(TYPE_NM , '*_CUSTOMER' )

     ;

SQL select * from ABC_table;

[rwunderlich · Accepted solution]

In QV syntax, the NOT should prefix the condition:

if ( NOT TYPE_NM  LIKE '*_CUST OMER', value) as filtered_value,

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#7]

Isn't "*_CUSTOMER" already a wildmatch or like command. Then why not just do

if ( TYPE_NM  <> '*_CUST OMER', value) as filtered_value?

I would like to learn the distinction in this, wildmatch and the like clause in QV.

Thanks for the answers and teaching.

[rwunderlich]

When using "<> '*_CUST OMER'", "*" is not a wildcard character. It is searching for the literal "*". "<>" is the operator and the right side of the operator is treated as exact text.

Like is a different operator. The value to the right of Like may have wilcard characters * & ?.

See "operators" in the Qlik Help for more info.

-Rob