Copy column to other table based on ID column

[#1]

Hello community,

With below two tables, how can I copy the CusName column to the Requester table based on both No/ID columns without joining them, so that I still have two separate tables? Note that for this example i removed other columns from both tables.

Input:

Customer:
LOAD * INLINE [
    CusNo, CusName
    001, Acer
    002, Microsoft
];


Requester:
LOAD * INLINE [
    ReqNo
    001
    002
    003
];

Desired Result:

Customer:
LOAD * INLINE [
    CusNo, CusName
    001, Acer
    002, Microsoft
];
Requester:
LOAD * INLINE [
    ReqNo, ReqName
    001, Acer
    002, Microsoft
    003, -
];

Thank you very much.

[sunny_talwar · Accepted solution]

May be using a mapping load?

Customer: 

LOAD * INLINE [ 

    CusNo, CusName 

    001, Acer 

    002, Microsoft 

];

Mapping:

Mapping

LOAD *

Resident Customer; 

Requester:

LOAD *,

  ApplyMap('Mapping', ReqNo, Null()) as ReqName;

LOAD * INLINE [ 

    ReqNo 

    001 

    002 

    003 

]; 

[hector_munoz]

Hi Thorsten:

Have you tried to use a LEFT JOIN?

LEFT JOIN    (Requester)
LOAD         Cusno     As Reqno, 
             CusName   AS ReqName
RESIDENT     Customer;

Regards,
H

[anushree1]

You Could probably use Keep instead of Join so that the join is done and also the two seperate tables are obtained

[#5]

this worked for me. Thanks a lot!