How To Link Two Different Sets of Files

[pra_kale]

Hi,

I am facing a problem in linking two different sets of Files

I am having one file which contains Premium related information and another file contains complaints information.  Both the files we can link through Channel, AVP and VP..but after linking these Two files I will be getting  out-put like below..which I not want..

Out-Put 1)...If I select AVP and VP names from combine_1 file

NewAVP	NewVP	Premium	Complaint
		277600	5
-	-	0	1
James	Mehandele	10000	1
Joseph	Ashu	10000	1
Sameer	Joshi	10000	1
Nitesh	Patel	12000	1
Rima	Nancy	235600	0

Out-put 2).. If I select AVP and VP names from combine file

AVP	VP	Premium	Complaint
		277600	5
Ramesh	Shetty	0	1
James	Mehandele	10000	1
Joseph	Ashu	10000	1
Sameer	Joshi	10000	1
Nitesh	Patel	12000	1
-	-	235600	0

so if you notice in either of these two tables VP and AVP name remains blank. But,I want name should appear either I will select AVP and VP  from combine file or NewAVP and NewVP from combine_1 file.

How I can achieve this...Please help.

Thanks in advance.

[Gysbert_Wassenaar]

Try this:

A:

LOAD Policy_no AS New_Policy,

     Premium,

     Year,

     Month,

     Channel,

     AVP,

     VP,

     Channel & '|' & AVP & '|' & VP as link     

FROM

[combine.xlsx]

(ooxml, embedded labels, table is Alernate);

B:

LOAD Policy_No,

     Complaint_No,

     Complaint_Description,

     Channel,

     AVP,

     VP,

   Channel & '|' & AVP & '|' & VP as link   

FROM

[combine_1.xlsx]

(ooxml, embedded labels, table is Alernate);

LinkTable:

LOAD DISTINCT

  link,

  Channel,

  AVP,

  VP

Resident A;

LOAD DISTINCT

  link,

  Channel,

  AVP,

  VP

Resident B;

DROP Fields Channel, AVP, VP FROM A;

DROP Fields Channel, AVP, VP FROM B;

EXIT Script

[pra_kale]

That's Great...It's working.

Thanks a lot..