Syntax for Multiple Records in Where Clause

[#1]

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...

[robert99]

You could try

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

[its_anandrjs]

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

[robert99]

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)

or

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)

or

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

[#5]

Hi David,

You can use any one from below options:

Load ...

From ...

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

or

Load ...

From ...

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

[#6]

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...

[robert99 · Accepted solution]

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

[maxgro]

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');

[ramasaisaksoft]

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.