Make a list variable out of a column field.

[eclecticfish]

Hi im a little stuck. Im making a qlikview which takes data from two different databases (oracle and MSSQL). I only want to take a limited amount of data from one of the DB's. So i take data from DB1 which produce some 2000 results, these have and ID which also exist on DB2 so basically i want a 

Select * from db2 where id in ($vListVarible)

where the $vListVarible is something like all the id's 

so set vListVarible = 1,2,3,4,5.........

so what im asking is how i do something like

set vListVarible = all the id's in DB1

[asinha1991]

you can use exist

if both ID fields have same name...

A:

select ID1, NAME from table 1;

B: 

Load * where exists(ID1,ID2); 

Select ID2, ADDRESS from table2 ;

if you still want to want to create string then

just use for loop and  peek. Example

let vListVarible =Peek(' ID' , 0, 'table1' )

for counter=1 to noOfRecords(table2);
let vListVarible=$(vListVarible)&','&Peek(' ID' , $(counter), 'table1' )
next

[Brett_Bleess]

Did Anand's post work for you?  If so, please be sure to give him credit by using the Accept as Solution button on his post.  If you are still working on things, leave an update, and if you did something different, consider posting that and then mark it as the solution, so others will know what did work in this use case.

Regards,
Brett

[Vegar]

To avoid the big  query on the SQL server it is a good thing to put the limitation to the SQL SELECT WHERE clause and not in the preseeding load WHERE clause.

You could try this.

[DB1]:
LOAD ID , DIM1, DIM2 , ETC 
FROM DB1;

TMP:
NOCONCATENATE LOAD 
  concat(distinct ID , ',') as listofIDs
RESIDENT
  [DB1];

LET vListVarible =peek('listofIDs');
DROP TABLE TMP;

DB2:
SQL SELECT 
  *
FROM DB2
WHERE
  ID in ( $(vListVarible))