topic Script - Why "FIELD like 'string*'" doesn't work ? in QlikView

Script - Why "FIELD like 'string*'" doesn't work ?

Wed, 25 Jan 2012 15:25:54 GMT

Hi guys,

I'm trying to load a table from a DB and I want to exclude some records.

To do that I thought to use the LIKE clause in my SQL.

Unfortunately it doesn't work.

I'm expecting 9 records and instead I have two strange results.

if I write:

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP;

SQL SELECT *

FROM CUST_VALUES

WHERE VALID_FOR_USER_GROUP LIKE '*Sesi*';

I have 0 records.

if I write:

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP;

SQL SELECT *

FROM CUST_VALUES

WHERE VALID_FOR_USER_GROUP LIKE '%Sesi%';

I have 6 records, NOT 9.

Any suggestions?

Thanks a lot

Giampiero

Script - Why "FIELD like 'string*'" doesn't work ?

stephencredmond — Wed, 25 Jan 2012 15:29:04 GMT

Hi Giampero,

SQL doesn't support * as a wild card - it uses %.

That is why the first one returns nothing and the second one works.

If you run the SQL (from SELECT * onwards) in a query tool, I would expect that you will only get 6 results also.

Regards,

Stephen

Script - Why "FIELD like 'string*'" doesn't work ?

Wed, 25 Jan 2012 15:34:20 GMT

Hi Stephen,

thanks a lot.

I tried to use * because on the manual there are some examples using * and I thought it was correct.

About the results with %.

I'm sure we have to obtain 9 records bacause I first tried to do the same Query on my DB, for this reason i'm not understanding the results.

Thank you so much for your help

Best regards

Giampiero

Script - Why "FIELD like 'string*'" doesn't work ?

stephencredmond — Wed, 25 Jan 2012 15:40:57 GMT

Hi Giampiero,

The reason that I think there will only be 6 records in the query is that QlikView is not responsible for collecting the records - the database driver is responsible to execute the query to the database engine and return the records to QlikView.

So, if you are only getting 6 records in QlikView, it is because there are only 6 records returned by the driver.

Regards,

Stephen

Re: Script - Why "FIELD like 'string*'" doesn't work ?

SunilChauhan — Wed, 25 Jan 2012 15:47:39 GMT

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP

WHERE wildmatch(VALID_FOR_USER_GROUP, '*Sesi*');

;

SQL SELECT *

FROM CUST_VALUES;

sorry steafen i have not see select staement

hope this helps

Script - Why "FIELD like 'string*'" doesn't work ?

stephencredmond — Wed, 25 Jan 2012 15:55:19 GMT

Hi Sunil,

First, your Wildmatch function is not formed correctly.

Secondly, Wildmatch is not a SQL function - it is a QlikView equivalent of a LIKE function (although QlikView also has LIKE).

You can include QlikView where clauses in a preceding load like this:

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP

Where WildMatch(VALID_FOR_USER_GROUP, '*Sesi*')

;

SQL SELECT *

FROM CUST_VALUES;

However, that would mean that all of the table rows are returned from the database before they are filtered by QlikView. This is less efficient than having the database engine filter them.

Regards,

Stephen

Re: Script - Why "FIELD like 'string*'" doesn't work ?

SunilChauhan — Wed, 25 Jan 2012 16:03:43 GMT

sorry steafen i have not see select staement

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP

WHERE wildmatch(VALID_FOR_USER_GROUP, '*Sesi*');

;

SQL SELECT *

FROM CUST_VALUES;

if we create a conneection then it creates the environment like data base

so check syntax like also needs to work

hope this helps