Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kalyandg
Contributor II

Expedite: More Databases More Tables

Hi Everyone,

I have a scenario, extracting data from 80 databases(postgres sql).

So I need an idea of doing this very easily, it means i have created 80 ODBC's, usually we connect to db in Qlikview script and extract the data. But now am not going to do as usual. Because it may consists too many tabs in script or we can split qvw.

So I got an idea of using some script for extracting the tables in a db, but i need to extract the tables from all the db.

Script for extracting the tables:

set db='"sample".public';
TableList:
SQL SELECT "TABLE_NAME"
FROM INFORMATION_SCHEMA.TABLES

where TABLE_SCHEMA='PUBLIC' AND TABLE_TYPE='BASE TABLE';
let rowCnt=NoOfRows('TableList');
FOR

I= 1 to $(rowCnt)

LET TblName = FieldValue('TABLE_NAME',$(I));

[$(TblName)]:
SQL SELECT * FROM $(DB)"$(TblName)";
if(NoOfRows('$(TblName)')>1) then
STORE [$(TblName)] into $(path)[$(TblName)].qvd;
ENDIF

DROP Table [$(TblName)];
NEXT

I;

But we face one error here, "TABLE_NAME" does not exist here.


The above script is for extracting the tables from a single db,

i need to include another scripting here for extracting tables from 80 db's.

Is it possible to do in Qlikview?


If yes, please reply me asap to fix the scenario.


Thanks and Regards,

Kalyan

Tags (1)
1 Solution

Accepted Solutions
kalyandg
Contributor II

Re: Expedite: More Databases More Tables

Hi all,

Hi,

The below script is used for extracting multiple tables from multiple dbs, its working.

SET V1='ODBC CONNECT TO xxxxx (XUserId is VWRYEYdNTDdOGZVMTTXB);';

set V2='ODBC CONNECT TO xxxxxx (XUserId is bLAJHYdNTDdOGZVMTbZB);';

FOR J= 1 to 2

$(V$(J));

TableList:

SQL SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

where table_schema='public' and TABLE_TYPE='BASE TABLE';


let rowCnt=NoOfRows('TableList');

FOR I= 1 to $(rowCnt)

LET TblName = FieldValue('table_name',$(I));

$(TblName):

SQL SELECT *

FROM db."public".$(TblName);

STORE $(TblName) into $(qvdpath)$(TblName).qvd;

DROP Table $(TblName);

NEXT I;

next J;


Now, am expecting to concatenate the tables, for example,

1st DB - GLTransaction

2nd DB -GLTransaction

.....

upto nth - GLTransaction

like this way, i need to concatenate the respective tables together and store it as qvd.


Is it possible for doing concatenation of  respective tables  from different db?

if yes please suggest me ideas.

7 Replies

Re: Expedite: More Databases More Tables

If you're using odbc you can use the Sqltables function. Here's an example using an excel file as odbc source to get the tables. Works the same for other odbc sources.


talk is cheap, supply exceeds demand
kalyandg
Contributor II

Re: Expedite: More Databases More Tables

hi Wassenaar,

I have postgres sql db, not excel....

Re: Expedite: More Databases More Tables

Oh, well if your postgresql database doesn't support odbc you should upgrade to a version that does have an odbc driver available. Every odbc driver supports the sqltables function.


talk is cheap, supply exceeds demand
kalyandg
Contributor II

Re: Expedite: More Databases More Tables

hi,

postgres sql odbc is available

kalyandg
Contributor II

Re: Expedite: More Databases More Tables

hi

postgres sql odbc is available, using that only i have created odbcs

see, my requirement is i need to extract all the tables from all dbs

but according to your script is it possible?

Regards,

Kalyan

Re: Re: Expedite: More Databases More Tables

Well, if you can't drive a green car because you had your driving lessons in a blue car....

ODBC CONNECT TO ...postgresql_database_connect_string_here...;

PostGreSQLTables:

SQLTables;

 

LET vRows = NoOfRows('PostGreSQLTables');

FOR i = 0 TO $(vRows)-1

    LET vTableName = subfield(peek('TABLE_NAME', i,'PostGreSQLTables'),'$',1);

    

    $(vTableName):

    LOAD * FROM $(vTableName);

NEXT i

DROP TABLE PostGreSQLTables;

DISCONNECT;


talk is cheap, supply exceeds demand
kalyandg
Contributor II

Re: Expedite: More Databases More Tables

Hi all,

Hi,

The below script is used for extracting multiple tables from multiple dbs, its working.

SET V1='ODBC CONNECT TO xxxxx (XUserId is VWRYEYdNTDdOGZVMTTXB);';

set V2='ODBC CONNECT TO xxxxxx (XUserId is bLAJHYdNTDdOGZVMTbZB);';

FOR J= 1 to 2

$(V$(J));

TableList:

SQL SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

where table_schema='public' and TABLE_TYPE='BASE TABLE';


let rowCnt=NoOfRows('TableList');

FOR I= 1 to $(rowCnt)

LET TblName = FieldValue('table_name',$(I));

$(TblName):

SQL SELECT *

FROM db."public".$(TblName);

STORE $(TblName) into $(qvdpath)$(TblName).qvd;

DROP Table $(TblName);

NEXT I;

next J;


Now, am expecting to concatenate the tables, for example,

1st DB - GLTransaction

2nd DB -GLTransaction

.....

upto nth - GLTransaction

like this way, i need to concatenate the respective tables together and store it as qvd.


Is it possible for doing concatenation of  respective tables  from different db?

if yes please suggest me ideas.

Community Browser