Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

amars
Valued Contributor

Sql's in clause can have Resident Load

Hi all,

I have a resident table, in which I have a list of values.

I need to use those values in the IN clause of SQL. like

SQL Select * from Table1 where Value1 IN ( Load Value2 Resident Master1);

please suggest.

I have some values coming from some different source which I need to use while loading another table.

Thanks in advance.

4 Replies
vijay_iitkgp
Valued Contributor

Sql's in clause can have Resident Load

Hi Amar,

For SQL  IN you can use wildmatch() in Qlikview.

Ex:

Load

*

Resident A where Wildmatch(Field1,'X','Y','Z').

But here you want to use result of subquery in the  IN clause. There is no direct way to to this in Qlikview.

You can do it by splitting this query in to 2 part.

part1:

T1:

Load

*

Resident A;

Left Join

Load

Field2 AS Field1,

Field2

Resident B;

T2:

Noconcatenate

Load

*

Resident T1 Where Field1=Field2;

Drop Table T1;

Hope this will help

Sql's in clause can have Resident Load

Hi,

     Try this approach.

     TempTable:

     Load

          Chr(39) & Concat(Distinct Value2,Chr(39) & ',' & Chr(39)) &Chr(39) as ConcatValue

     Resident

           Master1;    

     Let vValue2=FieldValue('ConcatValue',1);

     Drop Table TempTable;

     SQL Select * from Table1 where Value1 IN ( vValue2);

Hope it helps

Celambarasan

mohamed_cheikh
New Contributor

Re: Sql's in clause can have Resident Load

Hi amars,

Did you find any solution, I have the same issue.

Thx

Not applicable

Re: Sql's in clause can have Resident Load

Hi Celambarasan,

I find your solution working with just a bit change:

SQL Select * from Table1 where Value1 IN ( $(vValue2) );

Thanks very much!

Crystal