topic Re: Clarification on Left Joins in QlikView

Clarification on Left Joins

Wed, 13 Aug 2014 20:40:34 GMT

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

Re: Clarification on Left Joins

simenkg — Wed, 13 Aug 2014 20:46:53 GMT

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);

Re: Clarification on Left Joins

aveeeeeee7en — Wed, 13 Aug 2014 20:59:25 GMT

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

Re: Clarification on Left Joins

Wed, 13 Aug 2014 23:25:29 GMT

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.

Re: Clarification on Left Joins

Wed, 13 Aug 2014 23:26:28 GMT

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

Re: Clarification on Left Joins

Anonymous — Thu, 14 Aug 2014 01:53:44 GMT

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.

Re: Clarification on Left Joins

simenkg — Thu, 14 Aug 2014 16:21:08 GMT

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

Re: Clarification on Left Joins

aveeeeeee7en — Thu, 14 Aug 2014 17:01:23 GMT

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