topic Re: Pull data from multiple databases in QlikView

Pull data from multiple databases

Anonymous — Wed, 17 Apr 2013 19:04:33 GMT

I’m just starting out with Qlikview and trying to load our data from our financial application. We currently have about 900 databases in this system each with the exact same schema. Somehow I need to use a statement like the one below and loop through each database replacing D182 with a database number. Any ideas if this is possible and how it can be done?

APPYDET:

LOAD

trx_ctrl_num as _trx_ctrl_num_py,

sequence_id as sequence_id_py,

apply_to_num as _trx_ctrl_num,

amt_applied;

SQL select

trx_ctrl_num,

sequence_id,

apply_to_num,

amt_applied

from D182.dbo.appydet

Re: Pull data from multiple databases

alexandros17 — Wed, 17 Apr 2013 19:14:20 GMT

for idx = 1 to 900

let dbnum = 'D' & text(idx) & '.dbo.mytable' // format number with 3 digit if you need

...

select ...

...

from $(dbnum)

Re: Pull data from multiple databases

Anonymous — Wed, 17 Apr 2013 21:00:41 GMT

This works perfect when hard coding 1 to 900. How can I set db_num equal to a value that is returned by a SQL statement?

Re: Pull data from multiple databases

kaushiknsolanki — Thu, 18 Apr 2013 07:18:20 GMT

Try something like below.

Min_Max:

Select Min,Max from database.table1

let vMin = peek('Min',0,'Min_Max');

let vMax= peek('Max',0,'Min_Max');

for idx = $(vMin) to $(vMax)

let dbnum = 'D' & text(idx) & '.dbo.mytable' // format number with 3 digit if you need

...

select ...

...

from $(dbnum)

Regards,

Kaushik Solanki