Left Join duplicates records

[#1]

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!

[Gysbert_Wassenaar]

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.

[#3]

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!

[Gysbert_Wassenaar · Accepted solution]

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.

[#5]

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!

[#6]

I'm so sorry!

You were right!

I checked and saw that I do have duplicate values

Thanks again for your prompt reply!

[#7]

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

[#8]

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]

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