topic Select and Left Join in QlikView

Select and Left Join

Thu, 16 Sep 2010 21:02:45 GMT

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

Select and Left Join

johnw — Thu, 16 Sep 2010 21:14:07 GMT

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
;

Select and Left Join

Thu, 16 Sep 2010 21:39:51 GMT

Hi John

Thanks for u response, i test but i recieved this error

Syntax error, missing/misplaced FROM:
MyTable:
LOAD

Any idea?

Thanks

Select and Left Join

Thu, 16 Sep 2010 21:42:57 GMT

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

Select and Left Join

johnw — Thu, 16 Sep 2010 21:45:42 GMT

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.

Select and Left Join

Thu, 16 Sep 2010 22:03:03 GMT

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!

Select and Left Join

johnw — Thu, 16 Sep 2010 22:15:40 GMT

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
;

Select and Left Join

Thu, 16 Sep 2010 22:44:52 GMT

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

Select and Left Join

johnw — Thu, 16 Sep 2010 23:02:15 GMT

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.

Select and Left Join

Fri, 17 Sep 2010 16:19:14 GMT

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