topic Exclude values in SQL query in QlikView

Exclude values in SQL query

Wed, 01 Jul 2015 09:43:49 GMT

So i have the below section in my script, as I am trying to exclude Firms with the name ICAP in. But my wildmatch function doesn't seem to work. Is wildmatch the best way of doing this?

SQL SELECT Id,

AccountId,

Firm__c

FROM Contact

WHERE AccountId != NULL AND WILDMATCH(Firm__c, '*ICAP*')=0;

I have also tried this, which also does not work

SQL SELECT Id,

AccountId,

Firm__c

FROM Contact

WHERE AccountId != NULL AND NOT WILDMATCH(Firm__c, '*ICAP*');

What am I doing wrong?

Re: Exclude values in SQL query

pokassov — Wed, 01 Jul 2015 09:45:44 GMT

Hello!

Wildmatch is QV's operator, not SQL.

Try this

load

where wildmatch(...);

sql select ...;

Re: Exclude values in SQL query

swuehl — Wed, 01 Jul 2015 09:47:33 GMT

Try

SQL SELECT Id,

AccountId,

Firm__c

FROM Contact

WHERE AccountId != NULL Firm__c NOT LIKE '%ICAP%';

Re: Exclude values in SQL query

alexandros17 — Wed, 01 Jul 2015 09:48:35 GMT

Wildmatch is a qlik function so you cannot use into a Select statement.

You could do

Load

...

Where WILDMATCH(Firm__c, '*ICAP*')=0;

Select

... (Your query)

otherwiseyou can use only SQL

doing:

SELECT Id,

AccountId,

Firm__c

FROM Contact

WHERE AccountId != NULL AND not Firm__c Like '%ICAP&';

let me know

Re: Exclude values in SQL query

Wed, 01 Jul 2015 09:53:48 GMT

I am loading from Salesforce, so it needs an SQL statement.

The statement works absolutely fine without the wildmatch bit

Re: Exclude values in SQL query

PrashantSangle — Wed, 01 Jul 2015 09:59:47 GMT

Hi,

try

Firm_c not like '%ICAP%'

Regards

Re: Exclude values in SQL query

alexandros17 — Wed, 01 Jul 2015 10:00:37 GMT

use this

SELECT Id,

AccountId,

Firm__c

FROM Contact

WHERE AccountId != NULL AND not Firm__c Like '%ICAP%'

Re: Exclude values in SQL query

Wed, 01 Jul 2015 10:01:02 GMT

you missed the AND out, but it didn't work anyway

got this error

ERROR at Row:19:Column:40 unexpected token: LIKE

Re: Exclude values in SQL query

Wed, 01 Jul 2015 10:01:58 GMT

What is the SQL equivalent?

Re: Exclude values in SQL query

Wed, 01 Jul 2015 10:03:33 GMT

didn't work

ERROR at Row:19:Column:39 unexpected token: LIKE

Re: Exclude values in SQL query

Wed, 01 Jul 2015 10:06:34 GMT

Figured it out, through trial and error

Firm__c != '*ICAP*'

thanks for your help anyway guys! It got me there in the end