SQL select with left join or where clause

[Anonymous]

Hi

I'm trying to load data from SQL server to qlikview based on a list of IDs provided. The list of IDs, for example (1,2,3,4,5) is in an excel spreadsheet, I wanted to do a where clause  or left join in the SQL select statement such as select * from table A where ID in (1,2,3,4,5) or left joint ID  to only extract these data from SQL server. Because the data volume is huge, I don't want to extract everything from SQL then reduce what's loaded to qlikview in load statement.

Any idea how to achieve that?

Thanks

Yvonne

[mikaelsc]

you could create a variable containing a string of your values and use that in your sql select?

(steps 2 and 3 are the tricky part)

1. Load excel containing ID

2. create temp table containing a concat() function :

temp:

Load

concat(distinct ID,',') as FieldValuesString

resident Excel;

3. create variable containing your values

let vIndexes = peek('FieldValuesString',0,'temp') ;

4. use variable in where clause

Load *

;

sql select

*

from XXX

where ID in ('$(vIndexes)');

[vishsaggi]

May be do an inner join.

Like load your excel sheet then

INNER JOIN

SQL Select * From tablename;

Make sure your ID fieldname in your Excel sheet matches the ID fieldname in you SQL query. Like

Table1:

LOAD ID

From yourexcel;

INNER JOIN(Table1)

SQL Select ID, Field2, Field3....

From yourSourceTable;

[dwforest]

Load Excel file ids.

Table1:

LOAD ID

From yourexcel;

Then load other table

Table 2:

LOAD *

FROM your sql

WHERE EXISTS(ID);

[vishsaggi]

A small modification David, you cannot write like Load * From sql may be you can try using Preceding load like

Table2:

LOAD *

Where Exists(ID);

SQL Select *

FROM SourceTableName;

[mikaelsc]

sorry, your solutions still need to read full database tables.

using the variable trick will perform the filtering on select in the db (and thus improving perf)