Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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`;
...
So basically you want me to just put NOCONCATENATE between the two selections? Tried and that didn't work unfortunately
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';
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
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
Do you have a connection ODBC configured in your script ?
Do you can test this connection?
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
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
how to get all the table names in qlikview through script
can any one help me??