topic Why is LEFT JOIN creating extra rows? in QlikView

Why is LEFT JOIN creating extra rows?

Wed, 03 Jun 2009 20:06:59 GMT

Having a bad QV day today 😞

I have a master table with 100,000 rows and a lookup table with 100 rows linked by an ID field.

When I load both everything works fine with the lookup table having 100 unique ID's and QV performing as I would expect.

But if I load the lookup table then the master table and then try a left join to suck in a lookup field from the lookup table I end up with 10,000,000 rows in the master table!!! I might expect this if all the ID fields where blank or null, but they are definitely all populated with sensible data.

Lookup:

load

fieldA,

fieldB,

from lookup.qvd (qvd);

Master:

load

field1,

field2,

from master.qvd (qvd);

left join load

fieldA

resident Lookup;

Any help welcome 🙂 Thanks

Why is LEFT JOIN creating extra rows?

Anonymous — Wed, 03 Jun 2009 20:59:45 GMT

Hi isawyer,

You need to include the ID in the resident load that you perform the left join on, otherwise you don't have any matching fields for the join to perform like you expect. Instead you end up with a carthesian product, 100000*100 rows.

//JSN

Why is LEFT JOIN creating extra rows?

Wed, 03 Jun 2009 21:06:30 GMT

Hi JSN,

Yes, you are correct, my brain was assuming that QV knew about the common ID already and therefore didnt need to be told.

Thats one for the GOTCHA list

Thanks very much 🙂