Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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"?>
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)
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.
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.
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.
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)
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)
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
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"?>
I was able to fix this by changing Load * from T.qvd;
Load * from
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?