topic Re: Left Join duplicates records in QlikView

Left Join duplicates records

Sat, 02 Mar 2013 12:44:53 GMT

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!

Re: Left Join duplicates records

Gysbert_Wassenaar — Sat, 02 Mar 2013 13:10:18 GMT

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

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.

Re: Left Join duplicates records

Sat, 02 Mar 2013 13:38:08 GMT

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!

Re: Left Join duplicates records

Gysbert_Wassenaar — Sat, 02 Mar 2013 13:55:02 GMT

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.

Re: Left Join duplicates records

Sat, 02 Mar 2013 14:05:04 GMT

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!

Re: Left Join duplicates records

Sat, 02 Mar 2013 14:44:21 GMT

I'm so sorry!

You were right!

I checked and saw that I do have duplicate values

Thanks again for your prompt reply!

Re: Left Join duplicates records

Thu, 28 Aug 2014 15:12:36 GMT

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

Re: Left Join duplicates records

Wed, 12 Nov 2014 04:52:50 GMT

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!

Re: Left Join duplicates records

anbu1984 — Wed, 12 Nov 2014 05:00:29 GMT

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