How to use "in" syntax in qlikview script?

[#1]

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.

[perumal_41]

Hi arun use match function. for example use like this

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

Regards

Perumal A

[#3]

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;

[#4]

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

[#5]

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

[hic]

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

[#7]

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]

Hi Arun ,

Please Upload Sample Application  and  tell how result  you want .

Regards

Perumal A

[hic]

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

[#10]

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.