Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple Oledb connections from singlesheet

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

1 Solution

Accepted Solutions
Not applicable
Author

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"?>

View solution in original post

19 Replies
maxgro
MVP
MVP

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)

Not applicable
Author

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

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

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

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

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

Not applicable
Author

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"?>

View solution in original post

Not applicable
Author

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?