How to do a LEFT JOIN in Qlik Sense that will work like a real Left Join does in every other BI-Tool except this one

[asheppardwork]

My fellow qlik-a-teers, I need your help.

I have a issue that once again a query/DBA logic would solve very quickly but in Qlik-a-tania (aka Qlik Sense Feb 2022 ver) I can't seem to crack it.  I got this load script but the id is not the same in the three tables for all the records so instead of INNER KEEP; I tried Left Join and ended up with cartesian join and the data went from 119 Mb to 136 Gb.  I need to 'sometimes' join on id when they line up and 'sometimes' just bring over the rows in tables 2/3 when they don't.  In db land (or really every other BI Tool ever made for 30+ years; except this one) that is either a 'left outer join' or a 'full outer join' but in Qlik Sense I have no idea why it isn't working when I use LEFT JOIN (TABLE_NAME). INNER KEEP works but leaves out the mis-matched IDs; LEFT JOIN (TABLE) causes each table to re-join back on the top table and itself making each row 100 copies of itself until it reaches billions of rows.

Example:

Table-1
ID, Name, Org, Last_Day_Worked
123, Smith, Corp, 2023-04-12 12:00:00
456, Jane, Sales, 2023-04-11 12:00:00
789, Julie, IT, 2023-04-12 12:00:00

Table-2
ID, Schedule, TimeZone
123, M-F, PST
123.1, M-Th, EST
123.2, Fr, CST
456, M-F, PST
789, M-F, CST

Table-3
ID, Proj_Name,ProjStatus, StartDate
123, Sell, In-Progress, 20230402
123.1, Merger, Complete, 20230301
123.2, Purchase, Pending, 20230401
456, New_Fleet, In-Progress, 20230201
789, Upgrade_Laptops, Pending, 20230501

Output SB:
ID, Name, Org, Schedule, TimeZone, Proj_Name, ProjStatus
123, Smith, Corp, M-F, PST, Sell, In-Progress
123.1, , , , , Merger, Complete
123.2, , , , , Purchase, Pending
456, Jane, Sales, M-F, PST, New_Fleet, In-Progress
789, Julie, IT, M-F, CST, Upgrade_Laptops, Pending

or if possible

ID, Name, Org, Schedule, TimeZone, Proj_Name, ProjStatus
123, Smith, Corp, M-F, PST, Sell, In-Progress
123.1, Smith, Corp, M-Th, EST, Merger, Complete
123.2, Smith, Corp, Fr, CST, Purchase, Pending
456, Jane, Sales, M-F, PST, New_Fleet, In-Progress
789, Julie, IT, M-F, CST, Upgrade_Laptops, Pending

Sample LOAD:

 table_1:
NoConcatenate

LOAD
    id,
    name,
    org,
    Last_Day_Worked
    
FROM [lib://my.qvd](qvd);


INNER KEEP

//LEFT JOIN (table_1) causes a cartesian join?

 table_2:

load id,
    schedule,
	TimeZone

FROM [lib://my_other.qvd](qvd);


INNER KEEP
//LEFT JOIN (table_1) causes a cartesian join?

 table_3:

load id,
     Proj_Name,
     ProjStatus,
     StartDate
 

FROM [lib://my_other_other.qvd](qvd);

[BrunPierre · Accepted solution]

Table1:
Mapping LOAD ID,
Name
& '|' &
Org 

Inline [
ID, Name, Org, Last_Day_Worked
123, Smith, Corp, 2023-04-12 12:00:00
456, Jane, Sales, 2023-04-11 12:00:00
789, Julie, IT, 2023-04-12 12:00:00
];

Table2:
Mapping LOAD ID,
Schedule
& '|' &
TimeZone 

Inline [
ID, Schedule, TimeZone
123, M-F, PST
123.1, M-Th, EST
123.2, Fr, CST
456, M-F, PST
789, M-F, CST
]
Where WildMatch(ID,'*.*') = 0;

Table3:
LOAD *,

SubField(ApplyMap('Table1',ID,Null()),'|',1) as Name,
SubField(ApplyMap('Table1',ID,Null()),'|',2) as Org,
SubField(ApplyMap('Table2',ID,Null()),'|',1) as Schedule,
SubField(ApplyMap('Table2',ID,Null()),'|',2) as TimeZone

Inline [
ID, Proj_Name,ProjStatus, StartDate
123, Sell, In-Progress, 20230402
123.1, Merger, Complete, 20230301
123.2, Purchase, Pending, 20230401
456, New_Fleet, In-Progress, 20230201
789, Upgrade_Laptops, Pending, 20230501

];

DROP Field StartDate From Table3;

EXIT SCRIPT;

[asheppardwork]

Thank you sir for your example and screen shot, can you explain why the use of 'mapping' and 'wildmatch' =0?

[BrunPierre]

Aside it being my preference, importantly, because with mapping I need not worry about duplicate data as I something do with joining. Notice how it allows me to define what to return when a value is not found in the mapping table, in this case Null().

The WildMatch filtering was only to prevent IDs like 123.1 & 123.2 from coming through, hence displayed as null like the expected output.

[asheppardwork]

Thank you very much sir, this did exactly what I wanted!