Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to qlikview and working on creating a reporting tool. We have 50 stores, the data for which are stored in two different postgres server. For simplicity lets say server1 and server2 each has 25 stores. Each server has 25 schemas in whcih all the information about each store is stored in several tables. In server1 I have 25 stores in schema s1 to s25 and in server2 I have other stores from schema s26 to s50.
I am facing two chalanges:
1) Inefficient coding: crruently I connect and load data from each server for each store using code which looks like this:
ODBC CONNECT TO Server1;
//******Table1******
Sales:
LOAD companyid, itemid,
custid,
itemschedule,
itempayments,
itemamount,
overridereason
;
SQL
SELECT
companyid, itemid,
custid,
itemschedule,
itempayments,
itemamount,
overridereason
FROM db1.s1.tab1 Where noteorigin <> 0 and notestatus = 1
UNION
SELECT
companyid, itemid,
custid,
itemschedule,
itempayments,
itemamount,
overridereason
FROM db1.s2.tab1 Where noteorigin <> 0 and notestatus = 1
UNION
I join 23 (by changing schema number from s1 to s25) moreselect statements with UNION to get data from server1 and then do the samething for server2. And this is just to get sales talbes. I have several othertables to load for reporting purpose.
2) Second chalange is that even after doing this I gettwo saparate tables for sales data in table viwer. I would like to have sales data for all 50stores in one table in qlikview.
Can I create a loop which will run the query by loopingthrough schemas from s1 to s25?
Can I load all 50 stores in one table in qlikview?
Message was edited by: samarth.shah
Hi
Yes I did not realise that the dbs were on different servers. You could put the information to build the connection string into the inline table and connect in the outer loop.
I can't do a complete solution now - I can do it in a few hours time. I don't know much about Postgres connection strings, but I am sure that if I do the example with SQL Server strings you can work out what you need.
Regards
Jonathan
Hi
This is the sort of code you could use:
TableMap:
LOAD * Inline
[
Server, Schemas
db1, 25
db2, 25
];
For zi = 1 To NoOfRows('TableMap')
Let zServer = FieldValue('Server', zi);
Let zSchemaCount = FieldValue('Schemas', zi);
For zj = 1 To zSchemaCount
Sales:
SQL SELECT companyid,
itemid,
custid,
itemschedule,
itempayments,
itemamount,
overridereason
FROM $(zServer).s$(zj).tab1 Where noteorigin <> 0 and notestatus = 1
Next
Next
Adjust the contents of the inline load to match your requirements, If the schema naming is not numeric, you could put a server/schemaname lookup in the inline and iterate over that instead.
Hope that helps
Jonathan
Thank you, Jonathan. It reduced my code by a lot. However it works for each server individually. I dont know how to handle two connections. Where should I put CONNECT TO statements in the loop? so that all 50 store's sales table load into the Sales table in Qlikview.
Hi
Yes I did not realise that the dbs were on different servers. You could put the information to build the connection string into the inline table and connect in the outer loop.
I can't do a complete solution now - I can do it in a few hours time. I don't know much about Postgres connection strings, but I am sure that if I do the example with SQL Server strings you can work out what you need.
Regards
Jonathan
I see what you mean. I think I can create connection string using inline table. Thanks a lot.