Left Join is really inner join

[doherja]

Hello,

I have a table in QlikView like this:

Cust	Month
A	Jan
A	Feb
B	Jan
B	Feb

I then left-join facts to it with a statement like 'LEFT JOIN (Table 1) Load * Resident Table2.' Table 2 looks like:

Cust	Month	Units
A	Jan	10
A	Feb	20
B	Jan	15

If I save the resulting table into a QVD after this join, the QVD looks like this, as expected:

Cust	Month	Units
A	Jan	10
A	Feb	20
B	Jan	15
B	Feb	<Null>

But in QlikView after the load completes, there is no Feb associated with B. A table box (not chart!) looks like:

Cust	Month
A	Jan
A	Feb
B	Jan

If I select Cust B, Feb is grayed out. I need B/Feb to exist in the table, and I think LEFT JOIN should do it. What can I change?

Thanks

[BrunPierre]

Could you post the fact table as well.

[doherja]

Hi BrunPierre,

Fact table looks like:

Cust	Month	Units
A	Jan	10
A	Feb	20
B	Jan	15
B	Feb	<null>

although the app behaves as if that last row is not there.  (In fact, I didn't think it was in there, until I did a load with just a single customer, so that I could see the full table in the data model viewer so that I could answer your question.)  For example, if I add list boxes for Cust and Month, and select Feb, Customer B is grayed out.

Thanks

[doherja]

This does not help:

Fact2:
NoConcatenate Load
     Cust,
     Month,
     If(Units > 0, Units, 0) as Units
Resident Fact;
Drop Table Fact;

same result with:

     alt(Units, 0) as Units

[doherja · Accepted solution]

Figured it out.  The original table is missing some dimension columns that are needed later (in this simplified version, the first table in my question is actually the half-way point of my script.  Not exactly sure why they are needed, but when they are included, the app behaves as intended.