Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

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
Highlighted
Luminary
Luminary

Re: Sub load in a sql select

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
Highlighted
Luminary
Luminary

Re: Sub load in a sql select

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

Highlighted
Contributor II

Re: Sub load in a sql select

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