topic Re: Multiple databases connect for dashboard in QlikView

Multiple databases connect for dashboard

Mon, 05 May 2014 17:49:46 GMT

Hi,

I am creating dashboard from two databases, I created ODBC connection for one DB and its connected fine. I pulled table called Master.

In the second tab, I connected another ODBC connection for another DB and its connected fine. I would like to pull the same table called Master.

And wanted to union these two tables as both tables same kind of fields, First of all is it possible to do that? How can I find the Master is coming from first DB or second DB.

Pls let me know your valuable inputs.

Thanks,

Re: Multiple databases connect for dashboard

Clever_Anjos — Mon, 05 May 2014 17:57:28 GMT

Possible and common.

Just be carefull about field names (Case sensitive).

If two tables share the same fields, QlikView will concatenate them

Re: Multiple databases connect for dashboard

Clever_Anjos — Mon, 05 May 2014 17:59:01 GMT

To identify where data came from

ODBC CONNECT <STRING1>

LOAD

Field1,

Field2,

...,

'Base1' as Source;

SQL Select * from Master;

ODBC CONNECT <STRING2>

LOAD

Field1,

Field2,

...,

'Base2' as Source;

SQL Select * from Master;

Re: Multiple databases connect for dashboard

Mon, 05 May 2014 19:50:26 GMT

ODBC CONNECT TO MTR (XUserId is yyyyy, XPassword is yyyyy);

SQL SELECT *

FROM "Master";

CONCATENATE

ODBC CONNECT TO BTR (XUserId is xxxxxxxx, XPassword is xxxxx);

SQL SELECT *

FROM "Master";

Is this doing correct?? And I don't have db connectivity and not able to bring only required fields from the two ODBC connections. Not sure where can I select the required fields from these two ODBC.

I want rename table names as well?

pls suggest.

Thanks

Re: Multiple databases connect for dashboard

maxgro — Mon, 05 May 2014 20:12:56 GMT

try this

ODBC CONNECT TO MTR (XUserId is yyyyy, XPassword is yyyyy);

NewTableName:

SQL SELECT *

FROM "Master";

ODBC CONNECT TO BTR (XUserId is xxxxxxxx, XPassword is xxxxx);

CONCATENATE (NewTableName)

SQL SELECT *

FROM "Master";

LOAD data into QlikView

Re: Multiple databases connect for dashboard

Tue, 06 May 2014 05:28:14 GMT

Thanks Grossi,

How about the field names, is there any option where we can select required fields??

Thanks,

Re: Multiple databases connect for dashboard

jagan — Tue, 06 May 2014 05:37:48 GMT

Hi Madhu,

Instead of using * in select queries specify the name of the fields you required to load.

ODBC CONNECT TO MTR (XUserId is yyyyy, XPassword is yyyyy);

NewTableName:

SQL SELECT

Field1,

Field2,

FROM "Master";

ODBC CONNECT TO BTR (XUserId is xxxxxxxx, XPassword is xxxxx);

CONCATENATE (NewTableName)

SQL SELECT

Field1,

Field2,

FROM "Master";

Hope this helps you.

Regards,

Jagan.

Re: Multiple databases connect for dashboard

SunilChauhan — Tue, 06 May 2014 05:42:56 GMT

use preceeding load while fetchin frm db and remove fields which are not reuired after fetching code into qlikview

example below

Load

sql select *

from db

you can delete C

and load like below

load

a,b;

sql select * from db

hope this helps

Re: Multiple databases connect for dashboard

Tue, 06 May 2014 06:10:42 GMT

I don't have database permission to check that and not able to selecting fields in the script.

Thanks,

Re: Multiple databases connect for dashboard

jagan — Tue, 06 May 2014 06:17:43 GMT

Hi Madhu,

Click on Select button in Edit Script window, there you can select the required table and columns in the popup.

Regards,

Jagan.

Re: Multiple databases connect for dashboard

Tue, 06 May 2014 08:38:14 GMT

So are you suggesting me to reload the script and get the field names from the table objects and copy it in the script.

There is no option where I can pick the required fields before run the dashboard?

Thanks,

Re: Multiple databases connect for dashboard

SunilChauhan — Tue, 06 May 2014 08:43:32 GMT

it will only come when you create connection string

using Oledb

and after that when you selecting Data from dB using Select in qlikview

it will show you to check option for Preceeding load

hope this helps

Re: Multiple databases connect for dashboard

Sat, 10 May 2014 07:30:12 GMT

The Syntax Grossi and Jagan are not working and not showing the table names separately.

Pls let me know the best syntax.

Thanks,

Re: Multiple databases connect for dashboard

ToniKautto — Sat, 10 May 2014 09:31:46 GMT

Generally it can be a good idea to structure the loads as data layers. Practically this means that you have one QVW per data source, which then extracts the data from each source and stores the result into QVD files. A rule of thumb can be that one extract QVW should only utilize one connection.

In the next layer you load the different extract QVDs and transform them into one common structure. The transformed data can then also be stored to a second layer of QVD files.

In a final layer you then load the transformed QVDs and build your presentation layer.

This ETL (Extact Transform Load) structure allows you to minimize data transfer from sources, and enables building different transformed structures from the same source data without having to reaccess the source. This will be especially efficient when your deployments grow.

Re: Re: Multiple databases connect for dashboard

maxgro — Sat, 10 May 2014 10:18:03 GMT

the syntax for query sql databases depends on the dbms: SQL language is a standard (many standard SQL-86, SQL-92, ......) but every dbms has its own features

If you want more help, you can post your script, post the error, let we know which databases are you querying.

For now we only know you want to do an union of 2 Master tables from 2 different databases.

Above all the answers I see above can be ok or wrong.

ODBC CONNECT TO MTR (XUserId is yyyyy, XPassword is yyyyy);

NewTableName:

SQL SELECT

'SourceTable 1' as Source,

Field1,

Field2,

FROM "Master";

ODBC CONNECT TO BTR (XUserId is xxxxxxxx, XPassword is xxxxx);

CONCATENATE (NewTableName)

SQL SELECT

'SourceTable 2' as Source,

Field1,

Field2,

FROM "Master";