4 Replies Latest reply: Apr 18, 2012 4:40 PM by samarth.shah RSS

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

      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