topic Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time) in QlikView

Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

Tue, 19 Apr 2011 07:27:10 GMT

Hello,

I connect to about 40 different databases by ODBC.

I use an Input box in my LOAD-script to set a variable with the correct number for each company but with 40 companies and more companies coming in I'm starting to have trouble to keep track of which company have the right number. Today the code looks like the one below and every new company gets a new ELSEIF.

[CODE]

LET vCompanynumber = input(' Companynumber: ', ' Company ');

IF $(vCompanynumber)=01 THEN

ODBC CONNECT TO [Company 1] (XUserId is cFVKFRBGQJMEWcdMHLAA, XPassword is ADBDIRFMOLYcWYIGIG);

ELSEIF $(vCompanynumber)=02 THEN

ODBC CONNECT TO [Company 2] (XUserId is SQfTFRBGRZNEWcdMHDDA, XPassword is YJUMNRFMOLYcWYIGCG);

ENDIF

[/CODE]

I need help with 2 things:

1. I would like to get a dropdown-list or something with each company number and name (I would prefer ODBC Connection name but it's not necessary) and with an easy click to start the loading. I only load data from one company each time with this application.

2. For my second application I would like to load every company's settings and compare them. So I need a loop through all of the connections above.

Note: I'm maybe over my head here because I have learned everything I know from programming in this forum the last 3 years so I would prefer some explanations if you post code here. [:)]

Please help me.

/Peter

Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

sushil353 — Tue, 19 Apr 2011 07:36:29 GMT

instead of using if else you can use switch case .

HTH

SV:Re: Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

Tue, 19 Apr 2011 08:15:58 GMT


Sushil wrote:
hi
instead of using if else you can use switch case .
HTH<div></div>

Example plz?

Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

nathanfurby — Tue, 19 Apr 2011 09:02:06 GMT

Take a look at my answer here:

http://community.qlik.com/forums/t/44015.aspx

Should give you an idea of how you can store the connection strings externally and then use a variable or a selection to decide the connection string.

SV:Re: Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

Tue, 19 Apr 2011 09:46:34 GMT

But that dosent fix that I have to write the companynumber without a separate note on my desk with company name and number??

I want to select from a list that shows med the company number and name when I reload my application.

SV:Re: Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

nathanfurby — Tue, 19 Apr 2011 09:56:21 GMT

I don't understand. You write the connection string, company name and company number into an Excel file. You only have to do this once. Then load it into the QV doc and use it in a list for selecting reload. You may need to link the selection to a variable to then use in the load script.

SV:Re: SV:Re: Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

Wed, 20 Apr 2011 06:24:45 GMT

Normal 0 21 false false false SV X-NONE X-NONE

I understand your solution. I tried it but how do I get a selection before I load my application? In your post you write: "could then change this via Input Box etc. and even think of a clever way to link the connection number from the Connections table to the variable" but I don't want an inputbox (Not like the one in my own script anyway) . I want to see all possible selections and select witch company data I want to load in my application.

Let's say I'm not clever and show me an example of the code….

SV:Re: SV:Re: Loadscript to connect to 40+ separate ODBC connections (Loop and on at a time)

nathanfurby — Wed, 20 Apr 2011 10:04:25 GMT


LillaJag1975 wrote:I want to see all possible selections and select witch company data I want to load in my application.

How are you going to 'Select' which company data you want to load?