topic use a for ... next loop to set connection strings from an external file in QlikView

use a for ... next loop to set connection strings from an external file

Mon, 09 May 2011 19:46:33 GMT

Our data source is moving (again) from one IP address to another. I would like to set up a table to manage the connections and store the connection strings in an excel file so that every time this happens I just have to change the connection one place (the spreadsheet) instead of in each dashboard.

The connection string is like " OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID= ... " The concept works when I directly assign the connection string to the variable but not when I try to bring it in from a spreadsheet. I'm guessing the problem is in my for loop.

I thought I could use a for .. next loop to do this but have been unsuccessful. My code looks something like this.

Any help with what I am doing wrong would be greatly appreciated.

// load the connection strings from excel spreadsheet

Connections:

LOAD

varConnection,

ConnectString

From Connections.xls

(biff, embedded labels, table is Sheet 1$);

//loop through connections to set

for i=0 to NoOfRows('Connections') - 1

If varConnection = 'DB1' then

Let varDB1connect = ConnectString;

ELSEIF varConnection = 'DB2' then

Let varDB2connect = ConnectString;

Endif;

Next i;

//use connection to DB1

$(varDB1connect)

DBTable1:

Load

SQL Select * from DB1.TestTable;

// use connection to DB2

$(varDB2connect)

DBTable2:

Load

SQL Select * from DB2.Test2Table;

Re: use a for ... next loop to set connection strings from an external file

Oleg_Troyansky — Mon, 09 May 2011 20:13:35 GMT

As an alternative, I'd recommend using INCLUDE with a couple of text files instead of this loop:

$(INCLUDE=\connections\Connect1.txt)

LOAD...

$(INCLUDE=\connections\Connect2.txt)

LOAD

However, if you really want to use the loop, then your loop should use function peek(), - something like this:

//loop through connections to set

for i=0 to NoOfRows('Connections') - 1

Let varDB$(i)connect = peek('ConnectString', i, 'Connections');

Next i;

This way, for each value of i, you will peek the relevant value...

use a for ... next loop to set connection strings from an external file

markmccoid — Mon, 09 May 2011 20:14:07 GMT

HI,

Your loop looks good to me.

You should change your Let statements to Set and see how that works.

If varConnection = 'DB1' then

Set varDB1connect = ConnectString;

ELSEIF varConnection = 'DB2' then

Set varDB2connect = ConnectString;

Endif;

The Let statement is used to evaluate the statement to the right, while the Set statement is just used to store the info in your variable.

Hope this helps.

Mark

use a for ... next loop to set connection strings from an external file

Mon, 09 May 2011 20:48:44 GMT

Thanks to both of you for your help. The peek(...) worked. I ended up modifying it a bit to use it in the if statement as well since my real connection names were a bit more descriptive than DB1, DB2 and I have to connect to at least half a dozen different databases in some of the dashboards.

for i=0 to NoOfRows('Connections') - 1

If peek('varConnection', i, 'Connections') = 'DB1' then

Let varDB1connect = peek('ConnectString', i, 'Connections');

ELSEIF peek('varConnection', i, 'Connections') = 'DB2' then

Let varDB2connect = peek('ConnectString', i, 'Connections');

Endif;

Next i;

Thanks again for your help!!!

Vicky