topic Re: Multiple Oledb connections from singlesheet in QlikView

Multiple Oledb connections from singlesheet

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Multiple Oledb connections from singlesheet

maxgro — Wed, 09 Mar 2016 22:01:14 GMT

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

// first db

oledb connect ......

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)

Re: Multiple Oledb connections from singlesheet

Thu, 10 Mar 2016 02:19:34 GMT

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.

Re: Multiple Oledb connections from singlesheet

jagan — Thu, 10 Mar 2016 02:52:03 GMT

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.

Re: Multiple Oledb connections from singlesheet

jagan — Thu, 10 Mar 2016 08:58:36 GMT

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.

Re: Multiple Oledb connections from singlesheet

maxgro — Thu, 10 Mar 2016 09:25:25 GMT

I suggest to start simple with 2 db.

// first db

oledb connect ......

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)

Re: Multiple Oledb connections from singlesheet

luciancotea — Thu, 10 Mar 2016 09:29:01 GMT

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)

Re: Multiple Oledb connections from singlesheet

Kushal_Chawda — Thu, 10 Mar 2016 09:42:16 GMT

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

Re: Multiple Oledb connections from singlesheet

Thu, 10 Mar 2016 16:49:33 GMT

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];

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];

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];

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

Re: Multiple Oledb connections from singlesheet

Thu, 10 Mar 2016 18:31:20 GMT

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

can this list box be changed to

dbflag

is this possible?

Re: Multiple Oledb connections from singlesheet

maxgro — Thu, 10 Mar 2016 19:48:49 GMT

use an expression in listbox

=pick(match(dbflag,1,2,3), 'T', 'E', 'X')

Re: Multiple Oledb connections from singlesheet

Thu, 10 Mar 2016 20:13:44 GMT

Thanks for the reply. However I am not seeing what I want to.

Here is what I did:

ListBox properties

expression:

=pick(match(Instance,1,2,3,4,5,6),'T','E','X','S','P','D')

Here is what I see:

What I want to see is:

Is that possible?

Re: Multiple Oledb connections from singlesheet

Thu, 10 Mar 2016 21:16:05 GMT

all tables exist

Re: Multiple Oledb connections from singlesheet

jagan — Fri, 11 Mar 2016 00:52:50 GMT

HI,

In script try like this

load *,T as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

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

Like this change in all tables.

Re: Multiple Oledb connections from singlesheet

Fri, 11 Mar 2016 02:18:56 GMT

that issue is fixed. I am looking for renaming all field values on the display in the list box as i mentioned below.

Re: Multiple Oledb connections from singlesheet

psankepalli — Fri, 11 Mar 2016 02:55:19 GMT

Maybe you can use this method to open & disconnect OLEDB connections

//1st DB connection...

OLEDB CONNECT .....;

Table1:

SQL SELECT .... FROM table1;

Store Table1 into yourQVDPath \ Table1.QVD;

Drop Table Table1;

DISCONNECT;

//2nd DB Connection

OLEDB CONNECT .....;

SQL SELECT .... FROM table2;

Store Table2 into yourQVDPath \ Table2.QVD;

Drop Table Table2;

DISCONNECT;

Re: Multiple Oledb connections from singlesheet

jonathandienst — Fri, 11 Mar 2016 05:19:02 GMT

Dont overcomplicate things. You can load from multiple sources without having to push out qvd's:

OLEDB CONNECT TO ...;

table:

load *,1 as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

OLEDB CONNECT TO ...;

Concatanate(table)

LOAD *,2 as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

OLEDB CONNECT TO ...;

Concatenate(table)

LOAD *,3 as dbflag;

select TimeDate,Size_MBs,name from databasegrowth;

Re: Multiple Oledb connections from singlesheet

PradeepReddy — Fri, 11 Mar 2016 05:38:22 GMT

use the above code in General--> Field--> Expression, instead of in expression tab.

Re: Multiple Oledb connections from singlesheet

Kushal_Chawda — Fri, 11 Mar 2016 07:08:43 GMT

table:

Load *

From T.qvd(qvd);

concatenate(table)

Load *

From E.qvd(qvd);

concatenate(table)

Load *

From X.qvd(qvd);

new:

noconcatenate

load *,

if(dbflag='1','T' ,if(dbflag='2','E',if(dbflag='3','X'))) as Dbflagnew

resident table;

drop table table;

Now use Dbflagnew field in listbox

Re: Multiple Oledb connections from singlesheet

PradeepReddy — Fri, 11 Mar 2016 13:11:28 GMT

use the above code in General--> Field--> Expression, instead of in expression tab.