topic Re: SQL select with left join or where clause in QlikView

SQL select with left join or where clause

Anonymous — Wed, 06 Jun 2018 17:23:25 GMT

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

Re: SQL select with left join or where clause

mikaelsc — Wed, 06 Jun 2018 17:30:05 GMT

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)');

Re: SQL select with left join or where clause

vishsaggi — Wed, 06 Jun 2018 18:07:00 GMT

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;

Re: SQL select with left join or where clause

dwforest — Wed, 06 Jun 2018 18:11:45 GMT

Load Excel file ids.

Table1:

LOAD ID

From yourexcel;

Then load other table

Table 2:

LOAD *

FROM your sql

WHERE EXISTS(ID);

Re: SQL select with left join or where clause

vishsaggi — Wed, 06 Jun 2018 18:26:18 GMT

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;

Re: SQL select with left join or where clause

mikaelsc — Sun, 17 Jun 2018 17:14:35 GMT

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)