Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people:
I confused and need work in Qlik one sentece from MSSQL, with the same result.
The sentece is in MSSQL
select * from tableheader left join tableitems
on tableheader.tableheader_module = tableitems.tableitems_module
and tableheader.tableheader_code = tableitems.tableitems_code
and tableheader.tableheader_number = tableitems.tableitems_number
I try this but the only same field with the same name is user in both tables,
but is not primary key and when in the script send this sentece, and see
viewer of tables (ctrl+T) the unique link is between the field "USER"
CONNECT TO xxxxxx
SELECT * FROM tableheader ;
SELECT * FROM tableitems
How can use JOIN in Qlik
Thanks for all, regards
Hi John:
1- Thanks for all
2- At last, this work for my
SQL SELECT
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
,* from tableheader
;
SQL SELECT
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
, * from tableitems
;
Regards
César
Something like this, I suspect. But if this is all you're doing, I see no reason to join in QlikView instead of in SQL. I'm also not sure where the USER field comes into the picture. But if you have a USER field on both tables named exactly the same, and you don't want to join on it, you'll need to rename one of them.
MyTable:
LOAD
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
...
;
SQL SELECT * FROM tableheader
;
LEFT JOIN (MyTable)
LOAD
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
...
;
SQL SELECT * FROM tabletimes
;
Hi John
Thanks for u response, i test but i recieved this error
Syntax error, missing/misplaced FROM:
MyTable:
LOAD
Any idea?
Thanks
Sorry the complete error is
Syntax error, missing/misplaced FROM:
MyTable:
LOAD
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
...
MyTable:
LOAD
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
...
Regards
Can you post your actual script, or at least this section of it? And just in case you were confused by it, my code DOES have syntax errors, in that the '...' should be replaced with the rest of the fields you are selecting, and not copied directly into your code. It also confuses me that you had the table name and then a dot before the field name, so perhaps that's not correct, but I was just copying what you had in that case.
Edit: OK, from the complete error, unless you mean for the '...' to indicate that there are lines of error that you cut out, it looks like you didn't add the other fields from your table. You need to load the fields that you want, and not have '...' directly in the code.
The script
CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;
Extended Properties="Description=SQL3;
DRIVER=SQL Server;SERVER=SQL3;UID=;
APP=QlikView;WSID=SQL5;DATABASE=Emp;
Trusted_Connection=Yes";Initial Catalog=Emp];
MyTable:
LOAD
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
from tableheader
;
SQL SELECT * FROM tableheader
;
LEFT JOIN (MyTable)
LOAD
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
from tableitems
;
SQL SELECT * FROM tableitems
;
----------------------
The error
Cannot open file 'C:\Program Files\QlikView\Ejemplos\Documents\tableheader'
MyTable:
LOAD
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
from tableheader
-----------------------------------------------------------------------------------------------------------
Many Thanks!
CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;
Extended Properties="Description=SQL3;
DRIVER=SQL Server;SERVER=SQL3;UID=;
APP=QlikView;WSID=SQL5;DATABASE=Emp;
Trusted_Connection=Yes";Initial Catalog=Emp];
MyTable:
LOAD
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
REMOVE THIS LINE AND LIST THE OTHER FIELDS YOU NEED HERE
//from tableheader REMOVE THIS LINE TOO
;
SQL SELECT * FROM tableheader
;
LEFT JOIN (MyTable)
LOAD
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
REMOVE THIS LINE AND LIST THE OTHER FIELDS YOU NEED HERE
//from tableitems REMOVE THIS LINE TOO
;
SQL SELECT * FROM tableitems
;
This is the result:
First Error
Field not found - <tableheader.tableheader_module>
SQL SELECT * FROM tableheader
Second Error
Table not found
LEFT JOIN (MyTable)
LOAD
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
In the "Scrip Execution Progress"
MyTable << tableitems 711,256 lines fetched
Thanks, regards
OK, so replace the wrong field name with the RIGHT field name. Then the first table can load, which should resolve the second error as well.
Hi John:
1- Thanks for all
2- At last, this work for my
SQL SELECT
tableheader.tableheader_module as module
,tableheader.tableheader_code as code
,tableheader.tableheader_number as number
,* from tableheader
;
SQL SELECT
tableitems.tableitems_module as module
,tableitems.tableitems_code as code
,tableitems.tableitems_number as number
, * from tableitems
;
Regards
César