3 Replies Latest reply: Oct 11, 2018 5:50 AM by Christos Karras RSS

    Join SQL Select with Excel

    Sandra Pinto

      Hi,

      I am trying to Left Join data from server and from Excel, but I get an error.

      As you can see below there are several joins in the server and they are going well.

      But when I add the join from the excel, I get this error :

      "

      The following error occurred:

      Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near 'labels'."


      What is wrong in the code?

      Thanks a lot!

      Sandra

       


      Fin_ACCOUNTS:

      LIB CONNECT TO 'Sandra-Lavi (lavi_sandra)';


      LOAD *;

      SQL SELECT

      a.ACCOUNT,

          a.ACCNAME,

          a.ACCDES,

          a."SECTION",  

          a.ADJACCOUNT,

          a.VATSECTION,   

          a.ACNGROUP,

          a.RECDATE,

          a.COMPANY,  

          a.ADJACCOUNT2,    

          s."ORD" as ORD_Sections,

          s.SECNAME,

          s.ACCTYPE,

          s.SECTIONCODE,   

          AT.ACCTYPENAME,

          AT."ORD" as ORD_AccType,

          AT.BALTYPE,

          AT.ACCTOTAL,

          B.BALTYPECODE,

          B.BALTYPEDES,

          B.BALFLAG,

          B.COEF,

          B.EBALTYPEDES     

      FROM lavi.dbo.ACCOUNTS as a

      Left Join lavi.dbo.SECTIONS as s on a."SECTION" = s."SECTION"

      Left Join lavi.dbo.ACCTYPES as AT on AT.ACCTYPE = s.ACCTYPE

      Left Join lavi.dbo.BALTYPES as B on B.BALTYPE = AT.BALTYPE


      Left Join[lib://FinanceData (lavi_sandra)/PL\PL_Auxiliary.xlsx]

      (ooxml, embedded labels, table is Accounts) as Ex

      on Ex.AccountID = a.ACCNAME

      ;

        • Re: Join SQL Select with Excel
          Tomasz Truszkowski

          Load XLSX data independently of SQL. And if you don't need left join let the associative engine do it's work If you do - join using RESIDENT load.

          Tomasz

          • Re: Join SQL Select with Excel
            Juraj Misina

            Optionally:

            LIB CONNECT TO 'Sandra-Lavi (lavi_sandra)';
            
            
            Fin_ACCOUNTS: 
            LOAD *;
            SQL SELECT 
             a.ACCOUNT,
                a.ACCNAME,
                a.ACCDES,
                a."SECTION",   
                a.ADJACCOUNT,
                a.VATSECTION,    
                a.ACNGROUP,
                a.RECDATE, 
                a.COMPANY,   
                a.ADJACCOUNT2,     
                s."ORD" as ORD_Sections,
                s.SECNAME,
                s.ACCTYPE,
                s.SECTIONCODE,    
                AT.ACCTYPENAME,
                AT."ORD" as ORD_AccType,
                AT.BALTYPE,
                AT.ACCTOTAL,
                B.BALTYPECODE,
                B.BALTYPEDES,
                B.BALFLAG,
                B.COEF,
                B.EBALTYPEDES      
            FROM lavi.dbo.ACCOUNTS as a
            Left Join lavi.dbo.SECTIONS as s on a."SECTION" = s."SECTION"
            Left Join lavi.dbo.ACCTYPES as AT on AT.ACCTYPE = s.ACCTYPE
            Left Join lavi.dbo.BALTYPES as B on B.BALTYPE = AT.BALTYPE
            ;
            
            Left Join
            LOAD 
              AccountID     as a.ACCNAME,
              *
            
            from [lib://FinanceData (lavi_sandra)/PL\PL_Auxiliary.xlsx]
            (ooxml, embedded labels, table is Accounts);
            

             

            Hope this helps.

            Juraj

            • Re: Join SQL Select with Excel
              Christos Karras

              As you can see from Juraj example, there is a Load statement that you have to apply the join. Instead, you join in the connection string directly, which is not a valid Qlik statement. Actually, even the aliases you have after the XLSX connection attributes are not valid.

               

              BR

              Christos