3 Replies Latest reply: Jul 30, 2014 2:39 PM by Loren Wood RSS

    SQL: Concatenate, join and create linktable

      Hi

      I hope someone can help me. I got eight different accessfiles with data. I would like to create a linktable with all the data (date, age, sex, questions regarding information, quality and so on) except ID since they are numbered 1, 2, 3... and so on in all the eight files.

       

      I got some other data in the same qlikviewfile so when I have tried concatenate the information from these eight files have been added to a table with data from an excelfile...

       

      I have attached the code I have at the moment and a picture.

       

      Thanks in advance.

      /A

        • Re: SQL: Concatenate, join and create linktable
          Henric Cronström

          It seems to me that all eight tables contain the same type of data, but for different location. If so, you should concatenate them.

           

          You could probably do it in a For - Next loop:

           

           

           

          Set vConcatenate = ;

          For each vSource in 'Gopshus', 'Prästholmen', 'Simhall', 'Lomsmyren', 'Hemus', 'Sanda', 'Friluftsbad', 'Siljansleden'

                    OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\TEKNISKA FÖRVALTNINGEN\Tekniska vht 2010\Qlikview\$(vSource).accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False];

                    $(vConcatenate)

                    Load *,

                              '$(vSource)' as Source;

                    SQL SELECT * FROM `Undersökning`;

                    Set vConcatenate = Concatenate;

          Next vSource

           

           

          Note that I use the variable in the connect string.

          Note also that there is an additiona field 'Source' created, so that you can see where the data comes from.

           

          Good Luck!

           

          HIC