Multiple Oledb connections from singlesheet

[#1]

Hello,

I am new to qlikview. I am trying to create a dashboard that shd connect to 5 sqlserver instances. These 5 instances have same database names and tables. I want to connect to these 5 instances/databases/tables from a single sheet and create 5 line graph charts one per instance but on the same instance. Is this possi

[maxgro]

I think you can load the data from the 5 db in QlikView script; pseudocode

// first db

oledb connect ......

T:

load *, '1' as db;

SQl select some field from sometable;

// second db

oledb connect ......

concatenate (T)

load *, '2' as db;

SQl select some field from sometable;

// repeat for other 3 db

.......

then add your charts

you can use the db field to select the database (1,2,3,4,5)

[#3]

I tried it. Here is the error:

Table not found

Concatenate(E)

LOAD *,'3' as db

Also I dont want to mix all the resultsets to one single result set. Rather, I need data from each instance (each database) and generate 5 seperate graphs on the same Sheet.

[jagan]

HI,

In Qlikview if you open a new connection the old connection will be closed.  So you can use multiple connections like below

//1

OLEDB CONNECT (Connection string1);

Table1:

SELECT

* FROM TableName;

//2

OLEDB CONNECT (Connection string2);   /// First connection is closed

Table2:

SELECT

* FROM TableName;

'

'

'

'

''

Like this you can close any number of connections.

Regards,

jagan.

[jagan]

HI,

YOu have to use right table for the right database.  Check that table exists in database.  Can you attach whole script?  Is Table E exists?

Regards,

Jagan.

[maxgro]

I suggest to start simple with 2 db.

// first db

oledb connect ......

T:

load *, '1' as db;

SQl select some field from sometable;

// second db

oledb connect ......

concatenate (T)

load *, '2' as db;

SQl select some field from sometable;

try and post the script and, if any, the error you received (or the full .qvw with few data)

[luciancotea]

There's are several ways to do things, here's how you should do it. But first let me tell you why: because QlikView is an In-Memory tool and it uses a snow-flake data structure. Forget about relational model.

Step 1. You bring the data from you 5 servers into a single data model in QlikView. Add a column to all tables to identify the source (for example, a add a column named 'Server' with values 1..5 in it)

Step 2. In your dashboard, you use if() or Set Analysis in your expressions to refer to a specific server:

sum(if(Server = 1, Value))

sum({<Server = {1}>} Value)

[Kushal_Chawda]

1st create QVDs from Db tables

oledb 1

Table1:

load *,

       1 as DBFlag;

SQL select *

FROM table

Store Table1 into yourQVDPath \ Table1.QVD;

Drop Table Table1;

oledb 2

Table2:

load *,

       2 as DBFlag;

SQL select *

FROM table

Store Table2 into yourQVDPath \ Table2.QVD;

Drop Table Table2;

Like wise you create the 5 QVDs for all 5 Oledb connection

After creating QVDs now load all the QVDs

table:

Load *

From Table1.qvd;

concatenate(table)

Load *

From Table2.qvd;

.

.

.

concatenate(table)

Load *

From Table5.qvd;

.

Now You can create the Single line chart & Use DBFlag field as selection on front end

[#9 · Accepted solution]

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBA_Diagnostics;Data Source=Staging;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DB09;Use Encryption for Data=False;Tag with column collation when possible=False];

T:

load *,1 as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

store T into C:\Qlikview\T.QVD;

Drop table T;

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBA_Diagnostics;Data Source=preprod;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DB09;Use Encryption for Data=False;Tag with column collation when possible=False];

E:

LOAD *,2 as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

store E into C:\Qlikview\E.QVD;

Drop table E;

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBA_Diagnostics;Data Source=VM;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DB09;Use Encryption for Data=False;Tag with column collation when possible=False];

X:

LOAD *,3 as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

store X into C:\Qlikview\X.QVD;

Drop table X;

table:

Load *

From T.qvd;

concatenate(table)

Load *

From E.qvd;

concatenate(table)

Load *

From X.qvd;

No errors after loading but Not seeing any available fields other than a string that says:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

[#10]

I was able to fix this by changing Load * from T.qvd;

Load * from (qvd). This for E and X as well and it works fine.

However I added a listbox with dbflag to switch between instancesto view the charts and it shows as

dbflag

1

2

3

can this list box be changed to

dbflag

T

E

X

is this possible?