topic Re: load data from all tables of a database in QlikView

load data from all tables of a database

ali_hijazi — Thu, 15 Sep 2011 07:51:01 GMT

HEllo

I need to load data from all tables in a database

how can I loop through all tables to load the data in each single table in my script?

load data from all tables of a database

its_anandrjs — Thu, 15 Sep 2011 07:56:14 GMT

Hi,

Which type of tables it is means Excel,Sql or some other.

Rgds

Anand

load data from all tables of a database

ali_hijazi — Thu, 15 Sep 2011 07:57:09 GMT

sql server

load data from all tables of a database

its_anandrjs — Thu, 15 Sep 2011 08:10:11 GMT

Hi,

Use

for each File in filelist ()

Sql Load statement

next File

HTH

Rgds

Anand

Re: load data from all tables of a database

gandalfgray — Thu, 15 Sep 2011 09:00:31 GMT

Hi Anand

Have you tried using filelist to get tables from a sqlserver database?

Does that work?

According to the documentation:

The filelist mask syntax produces a comma separated list of all files in the

current directory matching the filename mask.

Ali,

I would suggest you to do something like:

LOAD "TABLE_CATALOG",

"TABLE_SCHEMA",

"TABLE_NAME",

"TABLE_TYPE";

SQL SELECT *

FROM master."INFORMATION_SCHEMA".TABLES;

Then you would have all tables in the TABLES table.

and could create a loop where you get each tablename into a variable using the peek function

and use that variable in a generic SQL Select statement.

hth

/gg

Re: load data from all tables of a database

Fri, 04 Nov 2011 10:17:58 GMT

This is my solution:

Tabelle:

SQLTABLES;

LET NTables = NoOfRows('Tabelle');

for i= 1 to NTables

LET Tabella = FieldValue('TABLE_NAME', i );

'$(Tabella)':
SQL SELECT * FROM DB.$(Tabella);

NEXT i

Re: load data from all tables of a database

matt_crowther — Fri, 04 Nov 2011 10:35:57 GMT

Not exactly the solution requested but I've created a Qlikview utility that does the requested and more.

It's a .qvw that first loads the sys.objects and sys.columns tables from a SQL datasource (even if the catalogue is hardened) presenting you with a front end where you select which table / views you wish to extract; make your selections hit reload and it extracts each selected table (or view) and stores it to QVD. (You can then use a simple '*' load from the QVD directory to load all teh tables into a new .qvw.)

This was designed for 2 reasons; extract data from SQL sources quickly and easily for off-site dev (really useful if you're in pre-sales) and secondly to allow the use of the columns field to quickly find source fields with a database - it gives you an isstant overview of the source db.

It should be straight forward to use, it's tested with SQL Server 2005+ and requires Excel 2003 or higher.

The password for the hidden script is 1234.

Hope it's of use.

Alt' Best,

Matt - Visual Analytics Ltd

Re: load data from all tables of a database

Fri, 04 Nov 2011 11:01:59 GMT

Nice but this is only for SQL datasource, try SqlTables and SqlColumns commands.