Clarification on Left Joins

[#1]

TABLE1:

Load

[Loan Number],

[Customer],

[Customer Invoice Number]

from [qvd\workspace\name.qvd].(qvd)

where match([Customer Invoice Number]>0;

TABLE2:

Load

[Loan Number],

[Maturity Date]

from [qvd\workspace\name2.qvd].(qvd);

I want to do a left join.  When I insert left join right before TABLE2 I get a table not found.  Essentially I have to create TABLE2 first.  This create two table in the table viewer and I get unwanted data (ie records with no Customer Invoice ID).  What would be the proper way to get a left join (all records in TABLE1 and matching records in TABLE2).  Essentially would I not be bringing together two resident tables as a left join

[simenkg]

TABLE1:

Load

[Loan Number],

[Customer],

[Customer Invoice Number]

from [qvd\workspace\name.qvd] (qvd);

/*where match([Customer Invoice Number]>0; this syntax is not correct and you have to change your where clause*/

left join(TABLE1)

TABLE2:

Load

[Loan Number],

[Maturity Date]

from [qvd\workspace\name2.qvd] (qvd);

[aveeeeeee7en]

Hi

First try to understand how Left Join works:

Suppose you have 2 tables:

Load *,

From Table1

Left Join(Table1)

Load *,

From Table2

It will give you below result:

Try this:

TABLE1:

Load

[Loan Number],

[Customer],

[Customer Invoice Number]

from [qvd\workspace\name.qvd].(qvd)

where [Customer Invoice Number]>0;

LEFT JOIN(TABLE1)

Load

[Loan Number],

[Maturity Date]

from [qvd\workspace\name2.qvd].(qvd);

Other thing, you need to check that which table you have to place above Table2 or Table1 depending upon your requirement.

Regards

Aviral Nag

[#4]

Let me make sure I understand you.  Are you saying create TABLE1 then do a left join to the same TABLE 1 then immediately load TABLE2 ?? Did you actually mean  left join TABLE2.

[#5]

So you are creating TABLE1 and a left join to the same TABLE1 ?

[Anonymous]

Join takes an [optional parameter] to specify which table to Join to.  If the optional parameter is left blank it joins to the table that was loaded immediately before it.

So when he typed JOIN(TABLE1) he is specifying to Join  TABLE2 to TABLE1.  If he had typed JOIN without the parameter  it would have done the same implied.

[simenkg]

That is the syntax for joining two tables. First you load one and then you preceed the load of the second table with the prefix join(Table1).

left join(Table1)

Load .... From Table2; This tells QlikView to find join table2 into table1. It will join on the fields with the same name in both tables and keep all values from table2 that matches fields in table1

[aveeeeeee7en]

You have not understood the concept yet.

See this:

I think, you are confusing between LEFT JOIN(TABLE1) syntax. It is not necessary to write TABLE1 along with LEFT JOIN. You can also Left Join using this:

TABLE1:

Load

[Loan Number],

[Customer],

[Customer Invoice Number]

from [qvd\workspace\name.qvd].(qvd)

where [Customer Invoice Number]>0;

LEFT JOIN

Load

[Loan Number],

[Maturity Date]

from [qvd\workspace\name2.qvd].(qvd);

Sometimes there are Tables with similar fields. So, in order to avoid the Joining between wrong tables, we specify Table Name for correct Joining. You can use this approach:

TABLE1:

Load

[Loan Number],

[Customer],

[Customer Invoice Number]

from [qvd\workspace\name.qvd].(qvd)

where [Customer Invoice Number]>0;

LEFT JOIN

Load

[Loan Number],

[Maturity Date]

from [qvd\workspace\name2.qvd].(qvd);

Hope that clarify your doubts.

Regards

Aviral