Mapping or ???

[#1]

I have two files as shown below

Customer Master File

CustID  Sales Person

123      U10

Budget File

CustID  Sales Person

123      U12

123R    U12

The Customer Master file shows U10 as the customer was reasigned to a different Sales Person.

I need the budget file to show U10 for both Customer IDs 123 and 123R.  Note: CustomerID 123R is not in the customer master file. It is a "fake" customer in the budget file that holds a rebate budget , hence the "R" after the real customer ID.

How would I accomplish this?

Thanks,

Stephen

[Anonymous · Accepted solution]

Stephen,

Mapping is fine, for example:

Map:
MAPPING LOAD DISTINCT
CustID as Input
"Sales Person" as Output
RESIDENT "Customer Master File";

Data:
LOAD
CustID,
applymap('Map', purgechar(CustID, 'R')) as "Sales Person"
RESIDENT "Budget File";

Regards,
Michael

[#3]

Yes, thanks Michael. I forgot that the applymap needed to be a "RESIDENT" table load.

Stephen

[eddysanchez]

[Customer Master File]:

LOAD * Inline [

CustID,  Sales Person

123,      U10

];

//[Budget File]:

Left Join ([Customer Master File])

LOAD CustID, [Sales Person] as [Sales Person Budget] Inline [

CustID,  Sales Person

123,      U12

123R,    U12

];

DROP Field CustID;

//[Budget File]:

Left Join ([Customer Master File])

LOAD CustID, [Sales Person] as [Sales Person Budget] Inline [

CustID,  Sales Person

123,      U12

123R,    U12

];