topic How to use "in" syntax in qlikview script? in QlikView

How to use "in" syntax in qlikview script?

Tue, 05 Mar 2013 07:06:36 GMT

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,

FROM

(qvd);

Any help is greatly appreciated.

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

perumal_41 — Tue, 05 Mar 2013 07:12:38 GMT

Hi arun use match function. for example use like this

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

Regards

Perumal A

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

Tue, 05 Mar 2013 07:20:27 GMT

try this

load name,

address,

FROM

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

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

try this example:-

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;

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

Tue, 05 Mar 2013 07:42:24 GMT

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

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

Tue, 05 Mar 2013 07:43:51 GMT

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

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

hic — Tue, 05 Mar 2013 07:55:44 GMT

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

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

Tue, 05 Mar 2013 11:19:05 GMT

Thanks Henric for sharing the link.

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

load name,

address,

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

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

perumal_41 — Tue, 05 Mar 2013 11:41:39 GMT

Hi Arun ,

Please Upload Sample Application and tell how result you want .

Regards

Perumal A

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

hic — Tue, 05 Mar 2013 11:58:23 GMT

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

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

Wed, 13 Mar 2013 11:47:39 GMT

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.

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

Wed, 13 Mar 2013 11:48:20 GMT

Hi Perumal,

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.

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

VishalWaghole — Wed, 13 Mar 2013 13:07:05 GMT

Hi,

Please try this one..

load name,

address,

FROM

(qvd) WildMatch(student_id,'12','76','89');

Thanks,

Vishal.

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

Wed, 13 Mar 2013 13:25:39 GMT

Thank You Vishal,

Please refer my exact query in the below link.

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

Please help me.

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

VishalWaghole — Wed, 13 Mar 2013 13:39:55 GMT

Hi Arun,

PFA of answer on link that you provide me, If still have doubt then revert to me.

Thanks and Regards,

Vishal Waghole