topic Re: Help me in below mapping scenario? in QlikView

Help me in below mapping scenario?

Tue, 22 Sep 2015 08:36:13 GMT

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.

Re: Help me in below mapping scenario?

Digvijay_Singh — Tue, 22 Sep 2015 08:57:44 GMT

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;

Re: Help me in below mapping scenario?

Digvijay_Singh — Tue, 22 Sep 2015 09:01:05 GMT

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;

Re: Help me in below mapping scenario?

sasiparupudi1 — Tue, 22 Sep 2015 09:09:38 GMT

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 ];

Re: Help me in below mapping scenario?

Tue, 22 Sep 2015 09:19:01 GMT

Great Sasi. its working.

thanks everyone.

Re: Help me in below mapping scenario?

sasiparupudi1 — Tue, 22 Sep 2015 09:22:43 GMT

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

Thanks

Sasi

Re: Help me in below mapping scenario?

Digvijay_Singh — Tue, 22 Sep 2015 09:29:08 GMT

Great! learned new thing. Thanks.