How to connect and load data from different servers and put them in one table in Qlikview?

[#1]

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

[jonathandienst]

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

[#3]

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. 

[jonathandienst · Accepted solution]

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

[#5]

I see what you mean. I think I can create connection string using inline table. Thanks a lot.