topic Re: SQL Select from several Database in QlikView

SQL Select from several Database

Tue, 19 May 2015 08:32:31 GMT

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.

Re: SQL Select from several Database

Anonymous — Tue, 19 May 2015 08:37:31 GMT

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.....

Re: SQL Select from several Database

Tue, 19 May 2015 08:50:20 GMT

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..

Re: SQL Select from several Database

Tue, 19 May 2015 09:05:18 GMT

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 ^^ )

Re: SQL Select from several Database

jagan — Tue, 19 May 2015 09:17:35 GMT

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.

Re: SQL Select from several Database

Tue, 19 May 2015 09:22:29 GMT

thanks jagan mohan

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

Re: SQL Select from several Database

jagan — Tue, 19 May 2015 09:25:25 GMT

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.

Re: SQL Select from several Database

shambhub — Tue, 19 May 2015 09:36:23 GMT

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

Re: SQL Select from several Database

Anonymous — Tue, 19 May 2015 09:37:52 GMT

-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.

Re: SQL Select from several Database

Tue, 19 May 2015 09:54:19 GMT

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

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

Re: SQL Select from several Database

Tue, 19 May 2015 09:56:36 GMT

Hi ShambhuB, thanks

With this solution i have to change the script and create a .txt for each of news databases. If it's was just for me ok, but it's for my colleagues, they are zero

Re: SQL Select from several Database

Anonymous — Tue, 19 May 2015 10:00:46 GMT

As you are "new" to qlikview, I'd suggest you start simple and just use 2 database sources and hard code it.

When you have got that working, tested and you are confident you understand it, then worry about the loop and the xls.

That is exactly how I started when I was building my solution.

Re: SQL Select from several Database

Tue, 19 May 2015 10:10:11 GMT

I don't think there is a simple user friendly way to do this.

I think you will need to generate some training materials for your users to assist them.

Re: SQL Select from several Database

Tue, 19 May 2015 12:26:14 GMT

I have my query with two databases concatenate

sql select * from base10_05.tableA concatenate select * from base14_05.tableA

(thanks for your help)

Re: SQL Select from several Database

Tue, 19 May 2015 12:28:17 GMT

be damned final user

Re: SQL Select from several Database

Tue, 19 May 2015 13:58:43 GMT

i've tried something like this :

A xls files connect to my server with the list of databases (sql show database) and a column choice where the user copy past the database that he want.

in my script :

//connexion a la base

ODBC CONNECT TO BasesPourQVW;

LOAD DataBases,

Choix

FROM

(biff, embedded labels, table is [Bases MySQL$]);

Let vbaseSelected = Choix;

hourlyvalue_time :

SQL SELECT *

FROM $(vbaseSelected).mod_bi_servicemetrics

JOIN $(vbaseSelected).mod_bi_metrichourlyvalue on $(vbaseSelected).mod_bi_servicemetrics.id = $(vbaseSelected).mod_bi_metrichourlyvalue.servicemetric_id ;

But vbaseSelected return nothing.. so -> sql error : No database selected

Re: SQL Select from several Database

jonathandienst — Tue, 19 May 2015 14:11:41 GMT

Use

Let vbaseSelected = 'Choix';

Set vbaseSelected = Choix;

Re: SQL Select from several Database

Tue, 19 May 2015 14:21:58 GMT

it return "choix" not the content

Re: SQL Select from several Database

Wed, 20 May 2015 07:14:02 GMT

i've done this, it works but i keep the topic not resolve if you have an other solution.

A xls files connect to my server with the list of databases (sql show database) and a column choice where the user copy past the database that he want.

in my script :

//connexion a la base

ODBC CONNECT TO BasesPourQVW;

LOAD DataBases,

Choix

FROM

(biff, embedded labels, table is [Bases MySQL$]);

Let vbaseSelected = peek('Choix',0);

hourlyvalue_time :

SQL SELECT *

FROM $(vbaseSelected).mod_bi_servicemetrics

JOIN $(vbaseSelected).mod_bi_metrichourlyvalue on $(vbaseSelected).mod_bi_servicemetrics.id = $(vbaseSelected).mod_bi_metrichourlyvalue.servicemetric_id ;

it makes a step for the user so it's not really optimal, but if there aren't other answer, it doesn't matter

Thanks everybody