Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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