Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
thi_pham
Creator III
Creator III

Sub load in a sql select

When I load many tables in a data load scripts, I ussually do this way:

Table1:

Load *;

SQL select a,b,c from datasource1 ;

 

Table 2:

Load * where exists (a);

SQL select a,e,f from datasource2 ;

 

The problem here is when the select in datasource2 is huge, I want to limit the value of select instead of select all then filter by where exists(a) in load later.

Is there any way to implement it like:

Table1:

Load *;

SQL select a,b,c from datasource1 ;

 

Table 2:

Load *;

SQL select a,e,f from datasource2 where a in (Load a resident Table1);

 

I tried some solutions, but still not success. An examble:

Table1:

Load *;

SQL select a,b,c from datasource1 ;

let vListA = Concat(a, ',') ; // Always return NULL

Table 2:

Load *;

SQL select a,e,f from datasource2 where a in ('$(vListA)');

 

Thanks.

Labels (1)
1 Solution

Accepted Solutions
Rodj
Luminary Alumni
Luminary Alumni

Hi @thi_pham ,

What you've tried should work if you get rid of the quotes around your variable. If you search community for "Dynamic SQL Query" you'll find examples.

That said, in general terms it's usually quicker to load the whole source table and use a Keep statement to reduce the result to what you want very quickly (note you might find that "keep" performs better than using "exists" if your data model allows). It depends on a whole host of factors such as the speed of your database, how your table is indexed, the resources available to Qlik server etc etc, but the in-memory database of Qlik doesn't have the transaction management overheads of a RDBMS and will usually be much faster. You'll need to test for your specific scenario to know for sure.

Cheers,

Rod

View solution in original post

2 Replies
Rodj
Luminary Alumni
Luminary Alumni

Hi @thi_pham ,

What you've tried should work if you get rid of the quotes around your variable. If you search community for "Dynamic SQL Query" you'll find examples.

That said, in general terms it's usually quicker to load the whole source table and use a Keep statement to reduce the result to what you want very quickly (note you might find that "keep" performs better than using "exists" if your data model allows). It depends on a whole host of factors such as the speed of your database, how your table is indexed, the resources available to Qlik server etc etc, but the in-memory database of Qlik doesn't have the transaction management overheads of a RDBMS and will usually be much faster. You'll need to test for your specific scenario to know for sure.

Cheers,

Rod

thi_pham
Creator III
Creator III
Author

Thanks so much @Rodj for your information. It works now, I change 2 points:

Table1:

Load *;

SQL select a,b,c from datasource1 ;

[temp]:

Load distinct concat(a, ',') as ValueLists resident Table1;

let vListA = peek('ValueLists',0,'temp'); // It have values this way

Table 2:

Load *;

SQL select a,e,f from datasource2 where a in ($(vListA)); // As you mentioned