SQL JOIN Syntax

[#1]

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?
 

[jaimeaguilar]

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

[#3]

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]

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]

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]

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 · Accepted solution]

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;

[#8]

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]

The Qlikview Reference Manual states:

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.

[#10]

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;