SQL Select from several Database

[#1]

Hi,

First sorry for my english..

I have 5 sql query, whose select many table. But now, i have an other database with the same name table.

I want to know if it's possible (with the qwd script or macro or other way), to give to the user the choice of the database ?

In the FROM of my query i have to put the name of the database before the table name, can i put a variable link to a listbox with "SQL SHOW DATABASE" ?

If you have any question i'm here, it's important.

cordially.

[Anonymous]

I would put the database name in a field, called say [Source Database] in the table created and then concatenate the data from the two source databases into a single table.

This field can then be used in the front end dashboard for selection, Set Analysis, Section Access etc.....

[#3]

thanks for your answer

The problem is for now I have 2 bases, but I can get over twenty and they are large (approximately 1Giga), so concatenate all the databases will be too big for the application..

[#4]

it's not possible tu put a variable in the query in the script, and choose this variable in a .bat whose lunch the application and load the data ? (just an idea ^^ )

[jagan]

Hi,

If you 5 sql queries are similar having same columns then try like below

Data:

SELECT

*,

'1' AS Database

FROM DataBase1;

Concatenate(Data)

SELECT

*,

'2' AS Database

FROM DataBase2;

Concatenate(Data)

SELECT

*,

'3' AS Database

FROM DataBase3;

Concatenate(Data)

SELECT

*,

'4' AS Database

FROM DataBase4;

Concatenate(Data)

SELECT

*,

'5' AS Database

FROM DataBase5;

Hope this helps you.

Regards,

Jagan.

[#6]

thanks jagan mohan

I have 5 sql query for each of my databases and i don't know the number of databases

[jagan]

Hi,

YOu have to add manually the queries everytime or else you have to store database details in some Excel file and using this loop through each row and load the data dynamically, everytime when a new database add it in the excel and for loop will load automatically.

Regards,

Jagan.

[shambhub]

Hi Jourdan,

As you said table names are same in both the databases. then you can easily switch the database based on the user selection.

Step 1: Create a connection string for the the database and store them in two separate text file.

Step 2: Call these connection string using Include function.

Step 3: Create a variable called var1 and add input box and assign variable var1 to input box to get the value from the user.

Step 4: Write the following code in Edit Script

             If (var1 = 1) then

                 $(Include=E:\Qlikview Documents\Connection String\DB1.txt);

             else

                $(Include=E:\Qlikview Documents\Connection String\DB2.txt);

               end

If you have any doubts, please let me know.

Best Regards

Shambhu B

[Anonymous]

-I do this successfully storing the database details in a xls and using a loop, as Jagan suggested.

It works great on 30+ databases, all of which are 10+ Gbytes and some 100+ Gbytes.

For each database I store a seperate qvd file which makes the incremental loads much easier than they would be otherwise.

Then in the qvw dashboard  I do optimized loads of them via a loop using the same xls and concatenate them all together.

QlikView works really well with such concatenated Fact Tables and with QlikView's inherent data field de-duplication will most likely be the most efficient solution - dependent on the actual data values, occurences & spread.

[#10]

Sorry but i'm "new" to qlikview, have you got a schema or a quick example ?

loop in the QWD   -->   XLS "details databases ?"   -->   QVD