Join SQL Select with Excel

[Anonymous]

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

;

[tomasz_tru]

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

[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

[ckarras22]

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