Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Join SQL Select with Excel

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

;

Tags (3)
3 Replies
Highlighted
Specialist
Specialist

Re: Join SQL Select with Excel

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

Highlighted
Luminary
Luminary

Re: Join SQL Select with Excel

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

Highlighted
Contributor III
Contributor III

Re: Join SQL Select with Excel

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