Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

Left Join duplicates records

Hi,

In what situations would there be additional records after Left Join?

Of course this is not what I want, but I have this issue.

I used Distinct, Where Exists in the Left Join load, but it didn't help.

There are still more records in the left table.

Thank You!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Left Join duplicates records

It's not a solution. It's an answer to your question: In what situations would there be additional records after Left Join?

Ok, based on you last comment I think what happens is like this:

Data:

load * inline [

Key, Value1

A, 1

A, 2

B, 2];

left join

load * inline [

Key, Value2

A, 10

A, 12

B, 15

B, 16

B, 20

C, 30];

The resulting table:

Key, Value1, Value2

A, 1, 10

A, 1, 12

A, 2, 10

A, 2, 12

B, 2, 15

B, 2, 16

B, 2, 20

Every A from the first table is joined with every A from the second table. It's not a cartesian product because it still matches on value, but you do get a lot more records.

You seem to indicate that that's not what you intend. So joining on only Customer Name is not enough. You'll have to add something to make the logic for the join explicit. Qlikview can't read minds yet so you have to tell it how to join the tables.


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
MVP & Luminary
MVP & Luminary

Re: Left Join duplicates records

In any case where there are multiple matching records in the table on the right side of the join.

Data:

load * inline [

Key, Value1

A, 1

B, 2];

left join

load Key inline [

Key, Value2

A, 10

A, 12

B, 15

B, 16

B, 20

C, 30];

The resulting table will look like

Key, Value1

A, 1

A, 1

B, 2

B, 2

B, 2

If you joined both tables but also loaded the field Value2 you would have the same number of records, but no duplicate rows since the added Value2 field would make the records unique.


talk is cheap, supply exceeds demand
Not applicable

Re: Left Join duplicates records

Hi,

I don't quite understand your solution.

My left table doesn't have unique key.

It has:

Data:

Date

[Customer Name]

Balance

....

I would like to add a customer number, from a file which has only customer number and customer name fields.

my left join is:

Left Join (Data)

load

[Customer Name],

[Customer Number]

Resident Customers;

I expected to have the additional field  [Customer Number]  in Data table.

I do, but there are additional rows. Doesn't look cartesian, though.

Would appreciate your help here!

Thanks!

MVP & Luminary
MVP & Luminary

Re: Left Join duplicates records

It's not a solution. It's an answer to your question: In what situations would there be additional records after Left Join?

Ok, based on you last comment I think what happens is like this:

Data:

load * inline [

Key, Value1

A, 1

A, 2

B, 2];

left join

load * inline [

Key, Value2

A, 10

A, 12

B, 15

B, 16

B, 20

C, 30];

The resulting table:

Key, Value1, Value2

A, 1, 10

A, 1, 12

A, 2, 10

A, 2, 12

B, 2, 15

B, 2, 16

B, 2, 20

Every A from the first table is joined with every A from the second table. It's not a cartesian product because it still matches on value, but you do get a lot more records.

You seem to indicate that that's not what you intend. So joining on only Customer Name is not enough. You'll have to add something to make the logic for the join explicit. Qlikview can't read minds yet so you have to tell it how to join the tables.


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

Re: Left Join duplicates records

Almost there 🙂

In your example, both tables have duplicate keys.

In my case, the 2nd table has a unique key (only one cusomer number per customer name).

Does it make a difference?

Thanks!

Not applicable

Re: Left Join duplicates records

I'm so sorry!

You were right!

I checked and saw that I do have duplicate values

Thanks again for your prompt reply!

Not applicable

Re: Left Join duplicates records

Thank you it was helpful. How to avoid duplicates, when left join is used

Not applicable

Re: Left Join duplicates records

Hi buddy,

I have got the same scenario in this case I want to select only one record from the second table .

Kindly help me out.

Thanks!

anbu1984
Honored Contributor III

Re: Left Join duplicates records

Table2:

load * inline [

Key, Value2

A, 10

A, 12

B, 15

B, 16

B, 20

C, 30] Where Not Exists(Key);

Right join(Table2)

Table1:

load * inline [

Key, Value1

A, 1

A, 2

B, 2];