topic Re: if statement equivalent of SQL not like in QlikView

if statement equivalent of SQL not like

Mon, 21 Dec 2015 18:23:38 GMT

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.

Re: if statement equivalent of SQL not like

JonnyPoole — Mon, 21 Dec 2015 19:05:14 GMT

Something like:

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

(note that i removed a space in CUSTOMER.

Re: if statement equivalent of SQL not like

JonnyPoole — Mon, 21 Dec 2015 19:06:18 GMT

typo:

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

Re: if statement equivalent of SQL not like

Mon, 21 Dec 2015 19:19:46 GMT

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

Re: if statement equivalent of SQL not like

maxgro — Mon, 21 Dec 2015 19:37:46 GMT

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;

Re: if statement equivalent of SQL not like

rwunderlich — Mon, 21 Dec 2015 19:43:41 GMT

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

Re: if statement equivalent of SQL not like

Mon, 21 Dec 2015 21:02:39 GMT

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.

Re: if statement equivalent of SQL not like

rwunderlich — Mon, 21 Dec 2015 22:06:44 GMT

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