Left Join not working as expected.

[#1]

Here is the script :

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;

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 ?

[#2]

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.

[SunilChauhan]

see the attached file

[#4]

Hi,

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

Thanks & Regards

Upendra

[#5]

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

[#6]

"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.

[#7]

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

[#8]

you can do like this:

A:

load * from tablename;

left join(A)

B:

load * from tablename;

it will generate what you want.

regards

[johnw]

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.

[#10]

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

Regards,

Abhinav