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

[wanyunyang]

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!

[dionverbeke]

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

[OmarBenSalem]

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]

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

[wanyunyang]

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.

[dionverbeke]

Can you tell us the error code or a screenshot?

[rwunderlich]

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]

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]

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>

[rwunderlich · Accepted solution]

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

-Rob