Combining two access tables together

[Anonymous]

Hi guys. I have a database with company information. This contains two tables: archive and master. What i'd like to do is combine archive and master into qlikview so i can search for companies. I have the following code:

Qualify*;

Current_SChemes:

CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MS Access

Database;DBQ=P:\Database.mdb;DefaultDir=P:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"];

SQL SELECT `Company Name` as [Comp],

`Company number` as [Comp #],

'Master' as [A or C]

FROM `P:\Database.mdb`.`Master`;

SQL SELECT `Company Name` as [Comp],

`Company number` as [Comp #],

'Archive' as [A or C]

FROM `P:\Database.mdb`.`Archive`;

Unqualify*;

Unfortunately when I use this code, it only brings back data from the Master database and not the archive. Can someone tell me where I’m going wrong?

[#2]

Try this:

...

SQL Selecionar Nome da empresa ` como [Comp],

`` Número Empresa como [Comp #],

'Mestre' como [A ou C]

DE `P:. \ Database.mdb` mestre `;

NOCONCATENATE

SQL Selecionar Nome da empresa ` como [Comp],

`` Número Empresa como [Comp #],

'Arquivo' como [A ou C]

DE `P:.` \ `Database.mdb Arquivo`;

...

[Anonymous]

So basically you want me to just put NOCONCATENATE between the two selections? Tried and that didn't work unfortunately

[#4]

When you use two tables with the same number of columns, the Qlikview automatically concatenate these two tables into one.

Do a test by inserting the sintaxe complete to verify that the records are being read correctly:

MASTER:

LOAD *;

SQL SELECT 'Company Name' as [Comp],

'Company number' as [Comp #],

'Master' as [A or C]

FROM 'P:\Database.mdb'.'Master';

ARCHIVED:

NOCONCATENATE

LOAD *;

SQL SELECT 'Company Name' as [Comp],

'Company number` as [Comp #],

'Archive' as [A or C]

FROM 'P:\Database.mdb'.'Archive';

[Anonymous]

As you recommended I changed the code to this

MASTER:

LOAD *;

SQL SELECT 'Company Name' as [Comp],

'Company Number' as [Comp #],

'Master' as [A or C]

FROM 'P:\Database.mdb'.'Master';

ARCHIVED:

NOCONCATENATE

LOAD *;

SQL SELECT 'Company Name' as [Comp],

'Company Number' as [Comp #],

'Archive' as [A or C]

FROM 'P:\Database.mdb'.'Archive';;

However I’m now getting an error:

ODBC connection failed

[jonathandienst · Accepted solution]

Hi

Do you want your data in one table? That's how I interpreted your post.

To do that, remove the Qualify * and Unqualify * statements and then QVs automatic concatenation will put them in the same table. You can verify the loading of the Archive by adding list box for field [A or C].

If the Archive is still not loading, then I would check the data source. Does the table have any content in F:\Database.mdb.?

Hope that helps

Jonathan

[#7]

Do you have a connection ODBC configured in your script ?

Do you can test this connection?

[jonathandienst]

re ODBC connection failed

It looks like you changed the back tics (field names) to quotes (strings). That's why I prefer square brackets for field delimiters.

Jonathan

[flipside]

Your original code is probably creating two QV tables, one called Current_SChemes and another named differently, so they are not concatenating.  This is due to the Qualify statements which prefixes the fieldnames with the table name.

If possible remove the QUALIFY if not required and the field names don't clash with any other previously loaded field.  If not possible, try this code in QV (my test is in Excel, but should give you the idea)  ...

Qualify *;
Current_Schemes:
LOAD [Company Name] as Comp,
     [Company number] as Comp#,
     'Master' as AorC
FROM
[Master.xls]
(biff, embedded labels, table is Sheet1$);
Unqualify *;

LOAD [Company Name] as Current_Schemes.Comp,
     [Company number] as Current_Schemes.Comp#,
     'Archive' as Current_Schemes.AorC
FROM
[Archive.xls]
(biff, embedded labels, table is Sheet1$);

However, maybe an easier solution is to UNION the tables in the SQL code ...

Qualify *;

Current_Schemes:

SQL SELECT `Company Name` as Comp,

    `Company number` as CompNo,

    'Master' as AorC

FROM `U:\QV docs\Sandpit Qlikview\Master`.`Sheet1$`

UNION SELECT `Company Name` as Comp,

    `Company number` as CompNo,

    'Archive' as AorC

FROM `U:\QV docs\Sandpit Qlikview\Archive`.`Sheet1$`;

Unqualify *;

flipside

[#10]

how to get all the table names in qlikview through script

can any one help me??