topic Re: Problem with "Left Join" in QlikView

Problem with "Left Join"

Tue, 23 Oct 2012 23:13:43 GMT

Hello:

I understand that a "Left Join" would result in all records in the first table, with the combination of field values from both tables. However, to me results in more records which contains the first table. I give detailed example:

1st table (PlanDemanda): 108,545 records

2nd. Table (Products): 1,540 records

and "Left Join": 109,313 records

Part of the example script is as follows:

PlanDemanda:

LOAD Week

SKUBase,

Plan

FROM

[C: \ Documents \ Data Source \ PlanDemanda.xlsx]

(OOXML, embedded labels, table is [Plan__2012]);

Left Join (PlanDemanda)

LOAD SKUBase,

CodigoCorto

Resident Products;

I need help to resolve the issue

Thanks

Re: Problem with "Left Join"

Anonymous — Tue, 23 Oct 2012 23:47:58 GMT

If Products contains multiple rows of a particular SKUBase, each record will be created in your resulting table, thereby increasing the overall recod count. And that record count would then be multiplied by the number of instances you have in the first table (PlanDemanda) for that particular SKUBase.

PlanDemanda:
LOAD * INLINE [
Week, SKUBase, Plan
1, A, 1
2, A, 1
];

LEFT JOIN (PlanDemanda)
LOAD * INLINE [
SKUBase, CodigoCorto
A, 1
A, 2
];

Hope this helps.

Re: Problem with "Left Join"

jeffmartins — Wed, 24 Oct 2012 02:56:53 GMT

Hi zenigarcia,

The second table has the same SKUBase value for different CodigoCorto id.

To keep the number of records in the first table you need to choose which CodigoCorto you want to include in the PlanDemanda table.

In my example I chose the greater CodigoCorto.

PlanDemanda:

LOAD Week

SKUBase,

Plan

FROM

[C: \ Documents \ Data Source \ PlanDemanda.xlsx]

(OOXML, embedded labels, table is [Plan__2012]);

Left Join (PlanDemanda)

LOAD SKUBase,

max(CodigoCorto) as CodigoCorto // choose which record you wanto to include PlanDemanda table

Resident Products

group by SKUBase;

Regards

Re: Problem with "Left Join"

Wed, 24 Oct 2012 03:56:40 GMT

Yes, that happens but only with 6 SKU but definitely the difference is greater. Not as attaching the qvw in this response. I do it in a new email

Re: Problem with "Left Join"

Wed, 24 Oct 2012 04:15:43 GMT

Attached the example

Re: Problem with "Left Join"

kaushiknsolanki — Wed, 24 Oct 2012 08:25:36 GMT

Hi,

The reason is the table "Producto" is having 4 SKU's which are repeated in the table.

The same SKU's are available in "Plan Demand" Table, thus when you are joining two tables the record count is increasing.

Take an example.

Table a:

Field A,Field B

1,XYZ

1,PQR

2,ABC

Left join

Field A, Field C

1, KKK

1, XXX

2, PQR

So the out put will be

Field A, Field B, Field C

1, XYZ, KKK

1, XYZ, XXX

1, PQR, KKK

1, PQR, XXX

2, ABC, PQR

As you see here the record count in final table should be 3, but actually it is 5, because your joining table is having multiple records for the field A.

Same is happening in your example.

You are having 6 SKU's which are repeaded in Producto Table.

and the Base table (Plan Demand) is having amlmost aroung 768 records for this SKU's.

So when you are joining them together you will get 768 records more then the actual count of your Plan Demand Table.

Hope this is clear.

Regards,

Kaushik Solanki