Problem with "Left Join"

[#1]

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

[Anonymous]

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.

[jeffmartins]

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

[#4]

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

[#5]

Attached the example

[kaushiknsolanki · Accepted solution]

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