topic Re: Union all tables from .MDB file? in QlikView

Union all tables from .MDB file?

Tue, 18 Mar 2014 09:04:40 GMT

Hello,

I have a MDB (access) file.
In this file i have multiple tables with the same structure (same fields).

I want to Load in QlikView all my talbes in a same table.

Is it possible?

I arrived to connect my MDB and launch SQL query but just on one table.

Thank you

Re: Union all tables from .MDB file?

cgdonders — Tue, 18 Mar 2014 11:38:21 GMT

Hi,

You can use the concatenate load functionality in your script.

If the columns are identical in each MDB table, QlikView will attempt to concatenate them by default if they are loaded in sequence.

Otherwise, name your first table, such as Tablename: then preface the following loads of other files with CONCATENATE (TableName)

Example:

TableName:

LOAD Col1, Col2

from [file.qvd];

CONCATENATE (TableName)

LOAD Col1, Col2

from [file2.qvd];

Re: Union all tables from .MDB file?

cgdonders — Tue, 18 Mar 2014 11:38:49 GMT

more information about concatenate: Merging tables: concatenation » The Qlik Fix! The Qlik Fix!

Re: Union all tables from .MDB file?

its_anandrjs — Tue, 18 Mar 2014 12:19:09 GMT

Hi,

Concatenate all tables and apply a flag field like

Example:

Table1:

LOAD ColA, ColB, ColC, 'Table1' as TableFlag

from YourLocation;

CONCATENATE (Table1)

LOAD ColA, ColB, ColC, 'Table2' as TableFlag

from YourLocation;

Like other tables

Thanks & Regards

Re: Union all tables from .MDB file?

Tue, 18 Mar 2014 12:20:24 GMT

I've tried this code :

ODBC CONNECT32 TO [MS Access Database;DBQ=D:\BDD\test.mdb];

TableName:
LOAD NUMBER
from ;

I have an error : Field not fount -<NUMBER>

Where is my error?

Re: Union all tables from .MDB file?

Tue, 18 Mar 2014 12:30:15 GMT

Ok,thank for your help, but the number of table is dynamic.
So today i have 20 tables but tomorow it could be 25 tables.

Is it possible to adapt your exemple ?

Re: Re: Union all tables from .MDB file?

maxgro — Tue, 18 Mar 2014 13:23:27 GMT

you have to loop on tables, something as (I read only one table, adapt to your requirements)

sqltables;

Let RCount = NoOfRows('T');

trace rows=$(RCount);

For i=0 to $(RCount) -1

let x = peek('TABLE_NAME',$(i), 'T');

trace table=$(x);

if '$(x)'='Origin Airports' then

trace read;

[$(x)]:

LOAD *;

SQL SELECT * FROM [$(x)];

ENDIF;

Next i;