topic Re: Left Join not working as expected. in QlikView

Left Join not working as expected.

Fri, 18 Nov 2011 05:49:31 GMT

Here is the script :

Load * Inline

[id, Category

0,cat0

1,cat1

2,cat2];

Load * Inline

[id, SubCategory

0,subCat0

1,subCat1

3,subCat3];

Left Join(A)

Load *

Resident B;

id	Category	SubCategory
0	cat0	subCat0
1	cat1	subCat1
2	cat2
3		subCat3

Was not expecting the row corresponding to id=3 here.

while :

Roght Join(A)

Load *

Resident B;

id	Category	SubCategory
0	cat0	subCat0
1	cat1	subCat1
3		subCat3

Could some one help me with it ?

Left Join not working as expected.

Fri, 18 Nov 2011 07:21:43 GMT

1. qlikview join is outer join unless specified which means the data common to both the tables in addition to data i.e not common to both the tables comes with null valus.

2. left join- common data from both the tables in addition to all the data of left table.

3. right join- common data from both the tables in addition to all the data of right table.

you tell me what type of data you want to see then i can tell you which join you have to use.

Re: Left Join not working as expected.

SunilChauhan — Fri, 18 Nov 2011 07:23:03 GMT

see the attached file

Re: Left Join not working as expected.

Fri, 18 Nov 2011 08:04:44 GMT

Hi,

What result you are expecting? both examples in your post gave the correct answer.

Thanks & Regards

Upendra

Re: Left Join not working as expected.

Fri, 18 Nov 2011 08:42:29 GMT

Could you explain why it is not happening the way I am trying to do ?

Re: Left Join not working as expected.

Fri, 18 Nov 2011 08:49:39 GMT

"The left join resulting table will only contain combinations of field values from the raw data tables with full set of data from the first table."

- Qlikview Help for left join

I was not expecting the row with id = 3 in the left join above.

Re: Left Join not working as expected.

Fri, 18 Nov 2011 09:53:23 GMT

Hi,

Pls try with the below code, this will help you.

A:
Load * Inline
[id, Category
0,cat0
1,cat1
2,cat2];
//B:
Left Join(A)
Load * Inline
[id, SubCategory
0,subCat0
1,subCat1
3,subCat3];

or in your script just drop table B

A:
Load * Inline
[id, Category
0,cat0
1,cat1
2,cat2];
B: 
Load * Inline
[id, SubCategory
0,subCat0
1,subCat1
3,subCat3];
Left Join(A)
Load *
Resident B;
drop table B;

Thanks & Regards

Upendra

Re: Left Join not working as expected.

Fri, 18 Nov 2011 10:13:25 GMT

you can do like this:

load * from tablename;

left join(A)

load * from tablename;

it will generate what you want.

regards

Left Join not working as expected.

johnw — Sat, 19 Nov 2011 00:46:29 GMT

abhiagar wrote:
I was not expecting the row with id = 3 in the left join above.

You haven't dropped table B, so you're still seeing all of table B's data. Upendra's two solutions to this problem look correct to me - either left join during the initial load without ever creating table B, or drop table B after the left join.

Re: Left Join not working as expected.

Tue, 05 Jun 2012 04:24:58 GMT

But why is not the same thing happening while doing a right join. I have not dropped A either.

Regards,

Abhinav

Re: Left Join not working as expected.

johnw — Tue, 05 Jun 2012 16:23:12 GMT

abhiagar wrote:
But why is not the same thing happening while doing a right join. I have not dropped A either.

Right join (A) resident B only keeps rows in A that are also in B. You're telling it you only want rows that exist in the "right" table (B). You should still drop B, but you don't see the extra row because it no longer exists.