9 Replies Latest reply: Nov 21, 2012 1:06 AM by Reshma Fatkare RSS

    Combining two access tables together

    Paul Wonford

      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

          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
              Paul Wonford

              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

                  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
                      Paul Wonford

                      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

                          Do you have a connection ODBC configured in your script ?

                           

                           

                          Do you can test this connection?

                          • Re: Combining two access tables together
                            Jonathan Dienst

                            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
                              Dave Riley

                              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
                        Jonathan Dienst

                        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