topic Sub load in a sql select in App Development

Sub load in a sql select

thi_pham — Sat, 16 Nov 2024 19:20:54 GMT

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.

Re: Sub load in a sql select

Rodj — Wed, 08 Jan 2020 03:59:09 GMT

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

Re: Sub load in a sql select

thi_pham — Wed, 08 Jan 2020 04:25:40 GMT

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