Outer Join not working

[TomBond77]

Hi experts

I have this script which should give as result also the records of the "Budget" file (right table "Budget") which are not existing in the left table "Excel Output". Any ideas where the problem is? The Join here shows results as it would be a LEFT Join.

[Excel Output_temp]:
LOAD *,
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key]

Resident [Excel Output];
Drop table [Excel Output];
Rename table [Excel Output_temp] to [Excel Output];

NoConcatenate
LOAD
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key],
QuantityFROM [lib://New Qlik budget 2024.xlsx]
(ooxml, embedded labels, table is Sheet1);

outer Join ([Excel Output])
Load *
Resident Budget;

[PrashantSangle]

difficult to guess,

What is use of below code in above script?

NoConcatenate
LOAD
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key],
QuantityFROM [lib://New Qlik budget 2024.xlsx]
(ooxml, embedded labels, table is Sheet1);

Just try below code & share there output

NoConcatenate

[Excel Output_temp]:
LOAD *,
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key]
Resident [Excel Output];

Join

Load *
Resident Budget;

Drop table [Excel Output];
Rename table [Excel Output_temp] to [Excel Output];

NoConcatenate
LOAD
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key],
QuantityFROM [lib://New Qlik budget 2024.xlsx]
(ooxml, embedded labels, table is Sheet1);

Regards,

Prashant Sangle

[TomBond77]

Thanks Prashant

Unfortunately your code is not working.

Below I explain the use explanation of my code:

****left table******

[Excel Output_temp]:
LOAD *,
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key]

Resident [Excel Output];
Drop table [Excel Output];
Rename table [Excel Output_temp] to [Excel Output];

****end of left table*****

***begin of right table****

Budget:

NoConcatenate
LOAD
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key],
QuantityFROM [lib://New Qlik budget 2024.xlsx]
(ooxml, embedded labels, table is Sheet1);

***right table has been loaded****

***start of the join to the left table****

outer Join ([Excel Output])
Load *
Resident Budget;

[PrashantSangle]

try this

[Excel Output_temp]:
LOAD *,
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key]

Resident [Excel Output];

Join

LOAD
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key],
QuantityFROM [lib://New Qlik budget 2024.xlsx]
(ooxml, embedded labels, table is Sheet1);

Drop table [Excel Output];
Rename table [Excel Output_temp] to [Excel Output];

[TomBond77]

Unfortunately not working, the data of the right table "Budget" are not loaded.

This code is working except the missing records from the right table:

****left table******

[Excel Output_temp]:
LOAD *,
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key]

Resident [Excel Output];
Drop table [Excel Output];
Rename table [Excel Output_temp] to [Excel Output];

****end of left table*****

***begin of right table****

Budget:

NoConcatenate
LOAD
date(floor("Timestamp"), 'DD.MM.YYYY') &'_'& "Cost Center Code" as [%Key],
Quantity

FROM [lib://New Qlik budget 2024.xlsx]
(ooxml, embedded labels, table is Sheet1);

***right table has been loaded****

***start of the join to the left table****

Join 
Load *
Resident Budget;

[marcus_sommer]

Joins or associations between tables with missing key-values on any side are usually not suitable.

Much simpler is often just to concatenate them - especially if they contain the same data like current (sales or whatever) data and their budgets - only the (view) direction of them is different. By simply adding an extra 'current' respectively 'budget' as SOURCE field to the load is enough to differentiate between them in dimensions, selections or set analysis. 

[TomBond77 · Accepted solution]

Thanks Marcus

this sounds interesting but I didn't get it 100%. What do you mean exactly?

Here is the current datamodel in QlikSense:

Where should I add a new column? Currently the key consist of "timestamp" and "Cost Center". On the left hand side (Facts1) the key is missing which is on the right hand side. Only two records of costcenter + timestamp keys are missing, the rest is very fine.

Thanks for your help!

Tom

[marcus_sommer]

Meant is to create a star-scheme with a single fact-table (which may have n surrounding dimension tables) and not having n fact-tables. Not only missing key-values are problematic with multiple fact-tables else also the associations between them and the dimensions are quite difficult and causing often serious side-effects.

Very often such scenarios are solved with a link-table between the fact-tables but it's a heavy detour for results which are much easier to get - like above suggested by concatenating the facts or maybe by quite asynchron fact-tables with an extra step of:

m: mapping key, return from B;

t: load *, applymap('m', key, 'default') as X from A;
concatenate(t) load key, field1, field2, return from B where not exists(key);