Help me in below mapping scenario?

[#1]

Hi All,

I have one mapping file with below fields.

Oldcustomer	new customer
ABCDE 2004122	ABCDEXX2004122
ABCDE 2005012	ABCDEXX2005012
ABCDE 2005376	ABCDEXX2005376
ABCDE 2016308	ABCDEXX2016308
ABCDE 2018353	ABCDEXX2018353
ABCDE 2020631	ABCDEXX2020631
ABCDE 2028220	ABCDEXX2028220
ABCDE 2028329	ABCDEXX2028329
ABCDE 2040712	ABCDEXX2040712

i need to map with another field custid

custid

ABCDE 2004122XXX-01

ABCDE 2005012

ABCDE 2005376XXX-01

ABCDE 2016308

ABCDE 2018353XXX-02

ABCDE 2020631

ABCDE 2028220

ABCDE 2028329XXX-02

ABCDE 2040712

now my scenario is to get the update the "custid" with new customer and the updated "custid" should retain 'XXX-01/02' as and where present.

output need to like below:

custid

ABCDEXX2004122XXX-01

ABCDEXX2005012

ABCDEXX2005376XXX-01

ABCDEXX2016308

ABCDEXX2018353XXX-02

ABCDEXX2020631

ABCDEXX2028220

ABCDEXX2028329XXX-02

ABCDEXX2040712

please advice me in mapping.

[Digvijay_Singh]

Try this :

Cust_map:

load Oldcustomer,newcustomer,PurgeChar(newcustomer,'XX') as mappingfield;

load * inline [

Oldcustomer newcustomer

ABCDE 2004122 ABCDEXX2004122

ABCDE 2005012 ABCDEXX2005012

ABCDE 2005376 ABCDEXX2005376

ABCDE 2016308 ABCDEXX2016308

ABCDE 2018353 ABCDEXX2018353

ABCDE 2020631 ABCDEXX2020631

ABCDE 2028220 ABCDEXX2028220

ABCDE 2028329 ABCDEXX2028329

ABCDE 2040712 ABCDEXX2040712 ] (delimiter is '');

inner Join

Load *,purgechar(left(custid,15),' X') as mappingfield;

Load * inline [

custid

ABCDE 2004122XXX-01

ABCDE 2005012

ABCDE 2005376XXX-01

ABCDE 2016308

ABCDE 2018353XXX-02

ABCDE 2020631

ABCDE 2028220

ABCDE 2028329XXX-02

ABCDE 2040712 ];

Drop fields mappingfield,newcustomer;

[Digvijay_Singh]

Just checked that you want to avoid space in output, pl replace below statements, it will remove in between space -

inner Join

Load PurgeChar(custid,' ') as custid,purgechar(left(custid,15),' X') as mappingfield;

[sasiparupudi1 · Accepted solution]

use mapsubstring

Cust_map:

mapping load * inline [

Oldcustomer,newcustomer

ABCDE 2004122,ABCDEXX2004122

ABCDE 2005012 ,ABCDEXX2005012

ABCDE 2005376, ABCDEXX2005376

ABCDE 2016308 ,ABCDEXX2016308

ABCDE 2018353 ,ABCDEXX2018353

ABCDE 2020631 ,ABCDEXX2020631

ABCDE 2028220 ,ABCDEXX2028220

ABCDE 2028329 ,ABCDEXX2028329

ABCDE 2040712 ,ABCDEXX2040712 ];

Cust:

Load * ,MapSubString('Cust_map',custid) as new_cust_id

inline [

custid

ABCDE 2004122XXX-01

ABCDE 2005012

ABCDE 2005376XXX-01

ABCDE 2016308

ABCDE 2018353XXX-02

ABCDE 2020631

ABCDE 2028220

ABCDE 2028329XXX-02

ABCDE 2040712 ];

[#5]

Great Sasi. its working.

thanks everyone.

[sasiparupudi1]

Great, In order help others, may I ask you please close this thread by marking a correct and any helpful answers?

Thanks

Sasi

[Digvijay_Singh]

Great! learned new thing. Thanks.