topic How To Link Two Different Sets of Files in QlikView

How To Link Two Different Sets of Files

pra_kale — Sun, 17 Jan 2016 13:15:03 GMT

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.

Re: How To Link Two Different Sets of Files

Gysbert_Wassenaar — Sun, 17 Jan 2016 17:21:21 GMT

Try this:

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

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,

Resident A;

LOAD DISTINCT

link,

Channel,

AVP,

Resident B;

DROP Fields Channel, AVP, VP FROM A;

DROP Fields Channel, AVP, VP FROM B;

EXIT Script

Re: How To Link Two Different Sets of Files

pra_kale — Mon, 18 Jan 2016 16:25:51 GMT

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

Thanks a lot..