Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

Re: How to connect and load data from different servers and put them in one 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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

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. 

MVP
MVP

Re: How to connect and load data from different servers and put them in one 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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

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