How to link customer table with Parent Customer

[manojkulkarni]

I have one scenario, where i need to link customer table with CustomerParent table, get parent name & id.

Below are the table details. Customers marked in bold & red, doesn't have any parent in parent table. But i have map these customers 'Others' & show it. How this can be achieved ?

Customer:

LOAD * INLINE [

  CustName,CID

    Cust1, 2100

    Cust2, 2101

    Cust3, 2102

    Cust4, 2103

    Cust5, 2014

];

CustomerParent:

LOAD * INLINE [

    GPID, GPName, CID

    1000, Customer1, 2100

    1000, Customer1, 2101

    1001, Customer2, 2102

];

[hic]

You could do the following:

Customer:

LOAD CustName,CID From Customers ;

CustomerParent:

LOAD GPID, GPName, CID, CID as FoundCID From Parents;

LOAD Null() as GPID, 'Others' as GPName, CID, CID as FoundCID From Customers

  Where not Exists(FoundCID, CID);

HIC

[#3]

Dear Manoj

I have attached a Qvw.

Is that what you are looking for?

Regards

[#4]

MAPS_PARENTS:

mapping LOAD distinct

CID,

GPID&'-'&GPName          as               PARENTINFO

resident Parents;

TRANS:

LOAD *,

subfield(PARENTSINFO,'-',1)          as               PARENTID,

subfield(PARENTSINFO,'-',2)          as               PARENTNAME;

Load *,

applymap('MAPS_PARENTS',CID,'Others')               as                    PARENTSINFO

resident Customers;

[preminqlik]

hi srashti add Others-Others in applymap

MAPS_PARENTS:

mapping LOAD distinct

CID,

GPID&'-'&GPName          as               PARENTINFO

resident Parents;

TRANS:

LOAD *,

subfield(PARENTSINFO,'-',1)          as               PARENTID,

subfield(PARENTSINFO,'-',2)          as               PARENTNAME;

Load *,

applymap('MAPS_PARENTS',CID,'Others-Others')               as                    PARENTSINFO

resident Customers;