Left Join Issue

[#1]

I am wanting to left join a resident table with a where clause that refers to a field in the table I am joining to (as per below). Qlikview doesn't recognise 'VacateDate_2', which is a field in table Lease_2. How do I get it to recognise the field and complete the join????

Left Join (Lease_2)

Load InspectionNoteType,

     PropCode,

     AreaCode,

     LinkKey,

     InspectionDate,

     MajorWork_Void

Resident ExitInspection

where InspectionDate > VacateDate_2;

[hic · Accepted solution]

This is not possible in one pass. But you can easily do it in two passes:

Left Join (Lease_2)

Load InspectionNoteType,

     PropCode,

     AreaCode,

     LinkKey,

     InspectionDate,

     MajorWork_Void

Resident ExitInspection;

Lease_2B:

Noconcatenate

Load *

Resident Lease_2

where InspectionDate > VacateDate_2;

Drop Table Lease_2;

[#3]

Thank you Henric, it has worked perfectly. Your help has been much appreciated.