Dynamically change data source

[#1]

Sorry if this is a total noob question, but i have just started using QLikView - I am evaluating it to see if it will fit my needs. One question I have not found the answer to yet on forums / manuals is: can I change the data source dynamically at run-time depending on the selection of a value in a table?

To explain more: I have 10 databases for 10 different divisions of a company. The databases are identical in structure, differ only in the data they contain. I want to be able to show the user a table of divisions (e.g. 'London', 'Manchester', 'Newcastle'), and depending on which one they select, I want to be able to connect to a different database to get my data from (i.e. change the source in my data script from 'ODBC CONNECT TO [London-SQL] to 'ODBC CONNECT TO [Manchester-SQL]')

For now I don't need to know HOW to do this, I only need to know IF this is possible in QlikView. Can anyone confirm this for me? Thanks for any help.

[#2 · Accepted solution]

Yes, that is possible with variables!

Here a small example how to connect to 3 databases and select the customer table from each database.
Just insert the variable in the connection string. So in your example when a user on the frontend changes the variable, the connection goes to the selected database..

for each vDatabase in 'Nordwind','DB1','DB2' CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\$(vDatabase).mdb;]; LOAD Region; SQL SELECT Region FROM Customer; next

[#3]

Fantastic. That's just what I need. Thank you so much!

[#4]

I had a doubt here. From where will you pass the database name to connection string in the Edit script window?.

Thanks,

Raghu.

[#5]

a) you can allow the user to select the database by setting the variable in an inputbox (see attached example)

b) with the Publisher you may/may not want to do a repeat task to generate seperate .qvws for each database!

[#6]

thanks for your reply. Let me try with my application and get back to you if i find any problem.

-Raghu.

[#7]

but it requires a reload after every selection. right?