
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
LEFT JOIN not working
I'm learning about QlikView JOINS but having a bit of trouble with this simple example:
I have a Main Table and would like to perform a left join to Table1 and Table2. The T-SQL statement would be:
SELECT m.* FROM MainTable m
LEFT OUTER JOIN Table1 t1 ON t1.ID = m.ID
LEFT OUTER JOIN Table2 t2 ON t2.ID = m.ID
I would like to know how QlikView handles the Color column from Tables 1 & 2. Will the output be:
ID Color
1 Red
2 Blue
3
This is my attempt:
Set NullInterpret = '';
MainTable:
LOAD * inline [
ID,Color
1,''
2,''
3,''
] ;
Table1:
LOAD * inline [
ID,Color
1,'Red'
];
Table2:
LOAD * inline [
ID,Color
2,'Blue'
];
Final:
LOAD
ID
,Color
Resident MainTable;
Left Join (Final)
LOAD ID, Color
Resident Table1;
LEFT JOIN (Final)
LOAD ID, Color
Resident Table2;
Getting the error:
Table not found
Left Join (Final)
LOAD ID, Color
Resident Table1
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Since Table1 and Table2 have the same structure as a previously loaded table i.e. MainTable(same no of columns and same fieldnames), they are getting auto concatenated to MainTable
Read below
MainTable:
LOAD * inline [
ID,Color
1,''
2,''
3,''
] ;
Table1: <<< Same structure as MainTable, so data from this load get auto-concatenated to MainTable . Table1 doesn't exist
LOAD * inline [
ID,Color
1,'Red'
];
Table2: <<< Same structure as MainTable, so data from this load get auto-concatenated to MainTable . Table2 doesn't exist
LOAD * inline [
ID,Color
2,'Blue'
];
Final:
LOAD
ID
,Color
Resident MainTable;
<<< At this point there is only one table in-memory i.e MainTable , Table1 and Table2 don't exist since they go auto concatenated to MainTable
Left Join (Final)
LOAD ID, Color
Resident Table1;
LEFT JOIN (Final)
LOAD ID, Color
Resident Table2;
Refer this article as well
https://community.qlik.com/t5/QlikView-App-Dev/Understanding-Join-Keep-and-Concatenate/td-p/328379
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
If all the fields are same in 2 tables, it will auto concatenate in Qlik.
You need to use noconcatenate in order to avoid the auto-concatenate functionality.
For ex:
Final:
NoConcatenate
LOAD
ID
,Color
Resident MainTable;
In your case, you can use Mapping table instead of joining
Please close the thread by marking correct answer & give likes if you like the post.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Since Table1 and Table2 have the same structure as a previously loaded table i.e. MainTable(same no of columns and same fieldnames), they are getting auto concatenated to MainTable
Read below
MainTable:
LOAD * inline [
ID,Color
1,''
2,''
3,''
] ;
Table1: <<< Same structure as MainTable, so data from this load get auto-concatenated to MainTable . Table1 doesn't exist
LOAD * inline [
ID,Color
1,'Red'
];
Table2: <<< Same structure as MainTable, so data from this load get auto-concatenated to MainTable . Table2 doesn't exist
LOAD * inline [
ID,Color
2,'Blue'
];
Final:
LOAD
ID
,Color
Resident MainTable;
<<< At this point there is only one table in-memory i.e MainTable , Table1 and Table2 don't exist since they go auto concatenated to MainTable
Left Join (Final)
LOAD ID, Color
Resident Table1;
LEFT JOIN (Final)
LOAD ID, Color
Resident Table2;
Refer this article as well
https://community.qlik.com/t5/QlikView-App-Dev/Understanding-Join-Keep-and-Concatenate/td-p/328379
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your reply. However I'm still getting the same error as before after trying your solution:
Table not found
Left Join (Final)
LOAD ID, Color
Resident Table1


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi,
i've not corrected anything in your script
Just posted in orange the explanation on what's happening during each load statement of your script
read the points in orange
If a post helps to resolve your issue, please accept it as a Solution.
