Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to use "in" syntax in qlikview script?

Hi friends,

I have the following query in sql syntax:

select name,address,id from student where student_id in (12,76,89);

How to convert this SQL query to qlikview script?

I have written upto here

load   name,

         address,

          id

FROM

(qvd);

Any help is greatly appreciated.

13 Replies
perumal_41
Valued Contributor II

Re: How to use "in" syntax in qlikview script?

Hi arun use match function. for example use like this

where match(student_id ,12,76,89)>0;

Regards

Perumal A

Not applicable

Re: How to use "in" syntax in qlikview script?

try this

load   name,

         address,

          id

FROM

(qvd) Where id = '12' or id = '76' or id = '89';

***************************************************************************************

try this example:-

a:

LOAD * INLINE [

    id, name, address

    10, a, a22

    11, b, d3

    12, c, e65

    13, d, q56

    76, e, r96

    80, f, g45

    89, g, p12

];

load   name as n,

     address as a,

      id as i

    Resident a

Where id = '12' or id = '76' or id = '89';

DROP Table a;

Not applicable

Re: How to use "in" syntax in qlikview script?

Thanks Viswaranjan,

Is your code returns the details with id's 12,76 and 89 or any one id with which it matches?

Because when i wrote the query in SQL with 'OR' Condition, the result will be the data with one of the matched id's.

So,i have changed to 'IN' condition where the result data will have all the id's

Not applicable

Re: How to use "in" syntax in qlikview script?

Thanks Perumal,

Is your code returns the details with id's 12,76 and 89 or any one id with which it matches?

Because i want student details with all the given id's

Employee
Employee

Re: How to use "in" syntax in qlikview script?

Perumal A is right. You should use the Match() function - or WildMatch() if you want wildcards. An improvement to Perumal's solution is if you remove the comparison with zero and just write

     Load ...

     Where Match(student_id ,12,76,89);

This will be evaluated faster. See more on

http://community.qlik.com/blogs/qlikviewdesignblog/2012/12/07/booleans

HIC

Not applicable

Re: How to use "in" syntax in qlikview script?

Thanks Henric for sharing the link.

How to do if have dynamic data for e.g.,

load   name,

         address,

         id

FROM

(qvd);

Inner Join

load  

          regd_id,

          age

FROM

(qvd) where match(regd_id,id,12,76,89);

My intention is to display the data if the regd_id matches with either id or 12 or 76 or 89.

Now, How to match with the id ?

Please Help

perumal_41
Valued Contributor II

Re: How to use "in" syntax in qlikview script?

Hi Arun ,

Please Upload Sample Application  and  tell how result  you want .

Regards

Perumal A

Employee
Employee

Re: How to use "in" syntax in qlikview script?

To link the tables you must rename your "regd_id" as "id". But you don't need the Inner Join.

I would use the Exist() function:

load name, address, id

FROM (qvd);

load regd_id as id, age

FROM (qvd)

          where Exists(id,regd_id) or match(regd_id,12,76,89);

 

HIC

Not applicable

Re: How to use "in" syntax in qlikview script?

Hi Henric,

Thank you for your reply but i didn't get the desired output.

I have explained the same Query in more detail in this link.

http://community.qlik.com/thread/74726

Please go through this page and guide me.

Community Browser