topic Re: Load ODBC-Connection-Path from a database in QlikView

Load ODBC-Connection-Path from a database

Tue, 06 Aug 2013 12:25:54 GMT

Hello!

Perhaps someone have a solution for the following problem:

Today i am loading data from many (+- 100) mdb-tables. In the parts of the script only

the path of the odbc connection is changing.

Example:

ODBC CONNECT32 TO [VCS Universal;DBQ=O:\Directory1\Subdirectory\ABC\LOG.mdb];

LOAD ...;

ODBC CONNECT32 TO [VCS Universal;DBQ=O:\Directory1\Subdirectory\DEF\LOG.mdb];

LOAD ...;

ODBC CONNECT32 TO [VCS Universal;DBQ=O:\Directory1\Subdirectory\GHI\LOG.mdb];

LOAD ...;

...

The path is also stored in a database. Would it be possible to load this scripts in a loop

and for every entry in the database of the directory?

Example Path-Directory:

ID, Path

1, O:\Directory1\Subdirectory\ABC\LOG.mdb

2, O:\Directory1\Subdirectory\DEF\LOG.mdb

3, O:\Directory1\Subdirectory\GHI\LOG.mdb

...

Re: Load ODBC-Connection-Path from a database

Tue, 20 Aug 2013 12:33:13 GMT

Hi manfred

I dont know if it has a solution. I tried it but I didnt found a solution.

I just know that you can do For each loop. But then you'll have alot of work with 100 Directories. It would look like this:

For each vDirectory in 'O:\Directory1\Subdirectory\ABC\LOG.mdb', 'O:\Directory1\Subdirectory\DEF\LOG.mdb', 'O:\Directory1\Subdirectory\GHI\LOG.mdb'

Load ....

FROM

[$(vDirectory)]

(biff, .....)

NEXT;

Re: Load ODBC-Connection-Path from a database

rbecher — Tue, 20 Aug 2013 13:52:56 GMT

Hi Manfred,

maybe this helps:

// replace with file or database source:
Databases:
LOAD * INLINE [
    ID, Path    
    1, O:\Directory1\Subdirectory\ABC\LOG.mdb
    2, O:\Directory1\Subdirectory\DEF\LOG.mdb
    3, O:\Directory1\Subdirectory\GHI\LOG.mdb
];

LET vNoRows = NoOfRows('Databases') -1;

for i=0 to $(vNoRows)
    let vDatabasePath = peek('Path',$(i),'Databases');
    
    ODBC CONNECT32 TO [VCS Universal;DBQ=$(vDatabasePath)]; 
    LOAD..;
next

- Ralf