topic macth() in where clause in QlikView

macth() in where clause

Mon, 16 Feb 2015 09:23:50 GMT

Hello,

i'm trying to load all record from a table where the type field matches either vNumber1 or vNumber2 using the folowing code:

set vNumber1 = 9624;

set vNumber2 = 9701;

LOAD

SQL Select *

from Table1

where match(type,$(vNumber1 ),$(vNumber2 ));

however, this results in the following message:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'match' is not a recognized built-in function name.

SQL Select *

from Table1

where match(type,9624,9701)

Why is the match function not working here?

Re: macth() in where clause

marcus_sommer — Mon, 16 Feb 2015 16:49:34 GMT

Match() is a qv-function and will be not recognized from the database or the odbc-driver. You will need use a sql-version from match(), maybe IN or apply the where-clause to a load-statement:

LOAD * where match(type,$(vNumber1 ),$(vNumber2 ));

SQL Select * from Table1;

- Marcus

Re: macth() in where clause

Wed, 18 Feb 2015 08:16:57 GMT

This seems to be working! Thanks.

After considering your code, it seems like loading the entire table before filtering is an unnecessary load on the source DB. I will therefore opt for direct filtering in the where Clause:

LOAD * ;

SQL Select * from Table1

where type = $(vNumber1 )

or type = $(vNumber2 ));