topic Re: Combining two access tables together in QlikView

Combining two access tables together

Anonymous — Fri, 05 Oct 2012 13:28:37 GMT

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?

Re: Combining two access tables together

Fri, 05 Oct 2012 13:30:36 GMT

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`;

...

Re: Combining two access tables together

Anonymous — Fri, 05 Oct 2012 13:46:21 GMT

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

Re: Combining two access tables together

Fri, 05 Oct 2012 14:02:53 GMT

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';

Re: Combining two access tables together

Anonymous — Fri, 05 Oct 2012 14:17:49 GMT

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

Re: Combining two access tables together

jonathandienst — Fri, 05 Oct 2012 14:24:00 GMT

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

Re: Combining two access tables together

Fri, 05 Oct 2012 14:25:15 GMT

Do you have a connection ODBC configured in your script ?

Do you can test this connection?

Re: Combining two access tables together

jonathandienst — Fri, 05 Oct 2012 14:26:54 GMT

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

Re: Combining two access tables together

flipside — Fri, 05 Oct 2012 14:28:28 GMT

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

Re: Combining two access tables together

Wed, 21 Nov 2012 06:06:40 GMT

how to get all the table names in qlikview through script

can any one help me??