Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL JOIN Syntax

I have two sql tables that I would like to join in my qlikview input script:

ODBC CONNECT TO [My Database];

[employee]:
SQL SELECT

      empID,field1

FROM table1;


LEFT JOIN (employee) SQL SELECT

     ID as empID,

     field2
FROM table2;

However, records are not joined from table2. This means qlikview is not looking at the alias "empID" in the second sql select. Is there a way to accomplish this join where keys do not have the same name in the source tables?
 

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

Did you try doing the JOIN in SQL?

SQL Select A.EmpID, A.Field1, B.Field2

        From Table1 A, Table2 B

        Where A.EmpID = B.ID;

View solution in original post

9 Replies
jaimeaguilar
Partner
Partner

Hi,

You can first extract your data to QVDs and then change field names so they have the same name and this way you can use QlikView Syntax,

regards

Not applicable
Author

Hi,

That's the exact step we are trying to avoid (extraction of qvd or csv files from source and then ingestion into qlikview). So is this not avoidable? One can not directly connect to the sql server and perform the join in qlikview?

fkeuroglian
Partner
Partner

Hi Dennis, try this

[employee]:

load empID,

        field1 ;
SQL SELECT

      empID,field1

FROM table1;


LEFT JOIN (employee)

load ID as empID,

        field2 ;

SQL SELECT

     ID as empID,

     field2
FROM table2;

see this example , good Luck

Fernando

-----------------------------------------------------------------------------

FSI006:

LOAD PGCOD,

     CICPO,

     PGCOD&''&CICPO as Key,

     RUBRO,

     CIPZOD,

     CIPZOH,

     CIPZOT;

SQL SELECT *

FROM FSI006;

Left  Join(FSI006)

LOAD

  PGCOD&''&CICPO as Key,

   CINOM,

   CITPOC,

   CITPOI;

SQL SELECT *

FROM FSI001;

jaimeaguilar
Partner
Partner

Well the qvd step is a best practice. However if you want to avoid this, you can do all in one step, without using QVDs. In that case, the script you post should work. If for some reason Qlikview is not recognizing EmpID in both tables you can use resident load as a workaround:

ODBC CONNECT TO [My Database];

[employee]:

Load *;
SQL SELECT

      empID,field1

FROM table1;


T2_temp:

Load *;

SQL SELECT

     ID as empID,

     field2
FROM table2;

Left Join(employee)

T2:

Load *, 1 as dummy; //this dummy avoids autoconcatenation with T2_temp, you can drop it at the end

resident T2_Temp;

drop field dummy;

drop table T2_temp;

regards

danieloberbilli
Specialist II
Specialist II

It might be a problem with the odbc driver in combination with the renamed field. Try to load both tables in QlikView like tab1 and tab2  and afterwards you do a join based on a resident load and drop the two previous tables again.

ODBC CONNECT TO [My Database];

tab1:
SQL SELECT

      empID,field1

FROM table1;

tab2:
SQL SELECT

     ID as empID,

     field2
FROM table2;

final_tab:

load * resident tab1, left joint(final_tab) load * resident tab2; drop tables tab1, tab2;

nagaiank
Specialist III
Specialist III

Did you try doing the JOIN in SQL?

SQL Select A.EmpID, A.Field1, B.Field2

        From Table1 A, Table2 B

        Where A.EmpID = B.ID;

View solution in original post

Not applicable
Author

Dear NagaianK,

Thanks for your suggestion. Although I got my original script running, your suggestion is the way to go from performance perspective as data size increases. I did not know you could use this syntax as QV ODBC wizard in script editor does not have "joining" option. Is this documented in the qv documentation? How did you find out about this? Great advice. I modified your script as follows to include the join statement:

SQL Select A.EmpID, A.Field1, B.Field2

        From Table1 A LEFT JOIN Table2 B

        ON A.EmpID = B.ID;

nagaiank
Specialist III
Specialist III

The Qlikview Reference Manual states:

Capture.PNG.png

So I have been using SQL statements with SQL keyword in my applications. As long as the SQL syntax is correct, I have had no issues so far.

Your SQL statement is correct for left join. Glad that you could resolve the issue.

Not applicable
Author

hi

you can also try according to this

[employee]:

SQL SELECT   empID, field1 FROM table1;

LEFT JOIN (employee)

load

ID as empID,

field2;


SQL SELECT    ID ,  field2  FROM table2;