Multiple table join

[calvin_wee]

Hi,

I am having 2 sets of data that I need to join them but not able to get the join right.

Load Table A

Left Join Table B

Concatenate Table C

Left Join Table D

Joining Table A, B, C is successful but when I LEFT JOIN Table D, all the data in D is loaded instead of Left Join.

Any Advise?

Thanks in Advance.

[hector_munoz]

Hi Calvin,

Could you attach the script sentences?

Regards,
H

[calvin_wee]

Hi,

Thanks for your reply. Please see below simplified form:

LOAD PRNo,

     PONo,

     Approver1;

[PRPO Information]

[SQL A]

left join ([PRPO Information])

LOAD Plant,
     CRFNo,
     PRNo,
     PRId,
     RequestorID,
     Requestor,
     RequestorMAIL,
     CostCtr,
     CostCtrName,
     Stage,
     BuyerCode,
     Buyer,
     PRDesc,
     VendorCode,
     VendorName,
     PRSubmitDT,
     InSystem;

[SQL B]

Concatenate

LOAD Plant,
     CRFNo,
     PRNo,
     PRId,
     RequestorID,
     Requestor,
     RequestorMAIL,
     CostCtr,
     CostCtrName,
     Stage,
     BuyerCode,
     Buyer,
     PRDesc,
     VendorCode,
     VendorName,
     PRSubmitDT,
     InSystem,
     ApprovalStageCode,
     Approver1,
     Approver2,
     ApproverStartDT;

[SQL C]

LOAD PRNo,
     CALRequired,
     MCFRequired;
    
left join ([PRPO Information])

[SQL D]

[hector_munoz]

Hi Calvin,

Is hard for me to see where is the problem without the full code. Maybe the problem is in some part of [SQL D]...

Regards,

H

[Kushal_Chawda]

try like below

[PRPO Information]:

LOAD PRNo,

     PONo,

     Approver1;

[PRPO Information]

left join ([PRPO Information])

LOAD Plant,
     CRFNo,
     PRNo,
     PRId,
     RequestorID,
     Requestor,
     RequestorMAIL,
     CostCtr,
     CostCtrName,
     Stage,
     BuyerCode,
     Buyer,
     PRDesc,
     VendorCode,
     VendorName,
     PRSubmitDT,
     InSystem;

Concatenate ([PRPO Information])

LOAD Plant,
     CRFNo,
     PRNo,
     PRId,
     RequestorID,
     Requestor,
     RequestorMAIL,
     CostCtr,
     CostCtrName,
     Stage,
     BuyerCode,
     Buyer,
     PRDesc,
     VendorCode,
     VendorName,
     PRSubmitDT,
     InSystem,
     ApprovalStageCode,
     Approver1,
     Approver2,
     ApproverStartDT;

   
left join ([PRPO Information])

LOAD *

FROM Source D;

Not sure about below part

[SQL C]

LOAD PRNo,
     CALRequired,
     MCFRequired;

Multiple table join

[calvin_wee · Accepted solution]

What happened is that when I commented the last statement and stops at SQL C, the data is correct.

When I continue with SQL D, instead of Left Join, I get 100% of SQL D data that is not desired.

Somehow it has become Right Join.

[calvin_wee]

Thank you all for your valuable inputs.

We managed to resolve as the wrong was putting LOAD before JOIN.

Once changed to JOIN then LOAD, it is ok.