Sql's in clause can have Resident Load

[amars]

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.

[vijay_iitkgp]

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

[CELAMBARASAN]

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

[Anonymous]

Hi amars,

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

Thx

[#5]

Hi Celambarasan,

I find your solution working with just a bit change:

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

Thanks very much!

Crystal