topic Re: Syntax for Multiple Records in Where Clause in QlikView

Syntax for Multiple Records in Where Clause

Mon, 03 Nov 2014 20:46:50 GMT

Hey All,

I have over 50 records in a field and I only want 10 of them loaded into the script. A few questions surrounding this --

1. Is the where clause the best way to do this?

2. What is the syntax for the where clause with multiple records. EX:

FROM....

WHERE UserName = "Dan" "Mike" "Joe" etc...

Re: Syntax for Multiple Records in Where Clause

robert99 — Mon, 03 Nov 2014 20:53:21 GMT

You could try

where match (UserName,'Dan','Mike', etc)

Re: Syntax for Multiple Records in Where Clause

its_anandrjs — Mon, 03 Nov 2014 20:53:47 GMT

Yes Where is best way you can try this way

Ex:-

Load *

From Location

Where Match(UserName,'Dan','Mike','Joe');

and write more strings if you have in the Match(Fieldname,'str',...)

Regards

Anand

Re: Syntax for Multiple Records in Where Clause

robert99 — Mon, 03 Nov 2014 20:58:28 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/17/and-or

(Product = UserName)

FROM .....

WHERE MATCH(Product,'Cap','Tracksuit'); for a load statement

And for select

where Product = 'Cap' ; (for one selection)

WHERE Product IN ('Cap','Tracksuit'); (for more than one)

OR FOR NOT

WHERE NOT MATCH(Product,'Cap','Tracksuit') for a load statement

And for select

where not Product = 'Cap' ; (for one selection)

WHERE Product NOT IN ('Cap','Tracksuit'); (for more than one)

Re: Syntax for Multiple Records in Where Clause

Mon, 03 Nov 2014 21:00:45 GMT

Hi David,

You can use any one from below options:

Load ...

From ...

Where match(UserName, 'Dan','Mike','Joe')

Load ...

From ...

Where UserName = 'Dan' or Customer ='Mike' or Customer='Joe'.....

Re: Syntax for Multiple Records in Where Clause

Mon, 03 Nov 2014 21:04:04 GMT

FROM SWKCRM.dbo.Opportunity

Where Match(Oppo_AssignedUserID,'129','147','171') ;

I get an error telling me that Match is not a pre-defined function...

Re: Syntax for Multiple Records in Where Clause

robert99 — Mon, 03 Nov 2014 21:09:10 GMT

Are you using select. If so try my option below

WHERE UserName IN ('John','Peter', 'etc'); (for more than one)

or use preceding load

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load

Re: Syntax for Multiple Records in Where Clause

maxgro — Mon, 03 Nov 2014 21:15:47 GMT

match is a Qlik function so you should use at the Qlik side:

Sql is executed first (bold), then Qlik (Italic) is executed using the result from sql (filter in Qlik)

Load *

where Where Match(UserName,'Dan','Mike','Joe'); // filter

SQL

Select *

FROM SWKCRM.dbo.Opportunity;

or filter in SQL; use the sql syntax of your database; no filter in Qlik

Load *;

SQL

Select *

FROM SWKCRM.dbo.Opportunity

where Username in ('Dan', 'Mike', 'Joe');

Re: Syntax for Multiple Records in Where Clause

ramasaisaksoft — Tue, 04 Nov 2014 06:09:13 GMT

Hi David,

In function is work in RDBMS but in QV we use the same function Match(fieldname,valu1,valu2,valu3,....)

both are same(IN()==match()).

i hope u understand if not examples are mentioned above with our friends.