Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
wanyunyang
Contributor II

SQL query doesn't work when loading data from ODBC into Qlik Sense

Hi guys,

I'm loading data from ODBC into Qlik Sense. My question is:

I have Table1 with columns A,B, Table2 with columns A,D. I want to left join Table2 on Table1 by column A, and show column  B and D.

The SQL query is like:

SELECT T1.B, T2.D

FROM Table1 T1 LEFT JOIN Table2 T2

ON T1.A=T2.A

But this query doesn't work in Qlik Sense data load editor. How can I fix it?

Thanks in advance!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

In the second statement you have renamed CustomerNo as CUSTOMER.  So you must refer to it as CUSTOMER in the LOAD statement.

-Rob

10 Replies
Luminary
Luminary

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

Did you have a LIB CONNECT TO statement in your script ?

It is also best practice to prefix your sql statement with a name, otherwise Qlik will assign a name.

SQL1:

SELECT T1.B, T2.D

FROM Table1 T1 LEFT JOIN Table2 T2

ON T1.A=T2.A

Partner
Partner

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

table1 and table 2 are Qlik's table? or table from ur source named as follow?

When u load data into Qlik; u'll have this syntax:

QLikTable:

Load

col1,

...

Coln ;

Select

col1,

...

Coln from yourSource;

In the select part, u can use the syntax u're usng in ur sql source like u're doing, working directly with the table of ur SOURCE.

in the LOAD part, u can use QLIK's SYNTAX and perform Joins of the QLIK's table

wanyunyang
Contributor II

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

I have both the LIB CONNECT TO statement and the SQL statement.

wanyunyang
Contributor II

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

Table1 and Table2 are tables from my database.

Is the grammar the same as in sql source? I think the problem occurs when I try to give alias to the columns in SELECT part.

Luminary
Luminary

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

Can you tell us the error code or a screenshot?

MVP & Luminary
MVP & Luminary

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

The grammar is the same, as everything after the "SQL" keyword gets passed through to the DB as is.  Could you explain what you mean by "doesn't work"?  Error message? Incorrect output?

-Rob

wanyunyang
Contributor II

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

This is the code that works well:

LOAD "Amount",

    "SalesQuoteDate",

    "CustomerNo";

SQL SELECT sq."Amount",

    sq."SalesQuoteDate",

    sq."CustomerNo"

FROM "salesquotes" AS sq

This is the code doesn't work:

LOAD "Amount",

    "SalesQuoteDate",

    "CustomerNo";

SQL SELECT sq."Amount",

    sq."SalesQuoteDate",

    sq."CustomerNo"  AS CUSTOMER

FROM "salesquotes" AS sq

The error code is:

The following error occurred:

Field not found - <CustomerNo>

wanyunyang
Contributor II

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

This is the code that works well:

LOAD "Amount",

    "SalesQuoteDate",

    "CustomerNo";

SQL SELECT sq."Amount",

    sq."SalesQuoteDate",

    sq."CustomerNo"

FROM "salesquotes" AS sq

This is the code doesn't work:

LOAD "Amount",

    "SalesQuoteDate",

    "CustomerNo";

SQL SELECT sq."Amount",

    sq."SalesQuoteDate",

    sq."CustomerNo"  AS CUSTOMER

FROM "salesquotes" AS sq

The error code is:

The following error occurred:

Field not found - <CustomerNo>

MVP & Luminary
MVP & Luminary

Re: SQL query doesn't work when loading data from ODBC into Qlik Sense

In the second statement you have renamed CustomerNo as CUSTOMER.  So you must refer to it as CUSTOMER in the LOAD statement.

-Rob