topic Left Join is really inner join in App Development

Left Join is really inner join

doherja — Thu, 30 Mar 2023 15:39:10 GMT

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

Re: Left Join is really inner join

BrunPierre — Thu, 30 Mar 2023 16:36:22 GMT

Could you post the fact table as well.

Re: Left Join is really inner join

doherja — Thu, 30 Mar 2023 16:51:47 GMT

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

Re: Left Join is really inner join

doherja — Thu, 30 Mar 2023 17:15:18 GMT

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

Re: Left Join is really inner join

doherja — Thu, 30 Mar 2023 18:09:14 GMT

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.