Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Left Join not working as expected.

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;

idCategorySubCategory
0cat0subCat0
1cat1subCat1
2cat2
3
subCat3

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

while :

Roght Join(A)

Load *

Resident B;

idCategorySubCategory
0cat0subCat0
1cat1subCat1
3
subCat3

Could some one help me with it ?

Tags (3)
10 Replies
Not applicable

Left Join not working as expected.

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
Esteemed Contributor

Re: Left Join not working as expected.


see the attached file

Not applicable

Re: Left Join not working as expected.

Hi,

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

Thanks & Regards

Upendra

Not applicable

Re: Left Join not working as expected.

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

Not applicable

Re: Left Join not working as expected.

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

Not applicable

Re: Left Join not working as expected.

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

Not applicable

Re: Left Join not working as expected.

you can do like this:

A:

load * from tablename;

left join(A)

B:

load * from tablename;

it will generate what you want.

regards

MVP
MVP

Left Join not working as expected.

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.

Not applicable

Re: Left Join not working as expected.

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

Regards,

Abhinav