topic Re: How to link customer table with Parent Customer in QlikView

How to link customer table with Parent Customer

manojkulkarni — Tue, 25 Feb 2014 06:28:45 GMT

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

];

Re: How to link customer table with Parent Customer

hic — Tue, 25 Feb 2014 06:38:46 GMT

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

Re: How to link customer table with Parent Customer

Tue, 25 Feb 2014 06:49:26 GMT

Dear Manoj

I have attached a Qvw.

Is that what you are looking for?

Regards

Re: How to link customer table with Parent Customer

Tue, 25 Feb 2014 07:08:47 GMT

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;

Re: How to link customer table with Parent Customer

preminqlik — Tue, 25 Feb 2014 07:17:57 GMT

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;