LEFT JOIN not working

[Berean_50]

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

[MayilVahanan]

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

[vinieme12 · Accepted solution]

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

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/LoadData/concaten...

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

[Berean_50]

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

[vinieme12]

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