Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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
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?
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;
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
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;
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;
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;
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.
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;