topic Re: Link between qvd and excel file in QlikView

Link between qvd and excel file

bharatkishore — Tue, 18 Jun 2013 13:31:33 GMT

Dear Member,

I am generating a field called "pubdate" from a qvd. In that pubdate i have fields starting from 01/04/2012 ,01/04/2012....to 31/05/2013. And i have two fields those are Fromdate and ToDate. I get these two fields from excel file.

Now in that FromDate if i take in list box i have two values i.e.01-04-2012

01-05-2013

Same if i take ToDate in list box means i have two values i.e. 31-03-2013

31-03-2014

Now i have field called rate this is also i am generating from excel. Now i need to have link between pubdate(field i am generating from qvd) and from date and todate(fields i am generating from excel). Please find the attached image.

Please help me on this.

Thanks and Regards,

R. Bharat Kishore

Re: Link between qvd and excel file

Gysbert_Wassenaar — Tue, 18 Jun 2013 15:58:38 GMT

Read this blog post: IntervalMatch

Re: Link between qvd and excel file

bharatkishore — Wed, 19 Jun 2013 05:53:27 GMT

Thanks for your reply if possible can you explain me more clear. I have written the following code:

Excel:

LOAD Type,

Competitor,

[Booking Unit],

[Client Group],

COL,

Edition,

State,

Page,

[From Date],

[To Date],

[Local Rate] as Rate,

[STC Rate] as STCRATE,

[GRC Rate] as GRCRATE,

Discount,

Premium,

[Edtion Type],

[Newspaper Name]

FROM (biff, embedded labels, table is [Final Sheet (2)$]);

Interval:

LOAD BKU_GROUP,

E_TERMIN,

PAGENO,

//Type

//Competitor,

//[Client Group],

//COL,

//Edition,

//State,

KEYPAGENO,

//[From Date],

//[To Date],

Rate,

STCRATE,

GRCRATE

//Discount,

//Premium,

//[Newspaper Name]

FROM (qvd);

IntervalMatch:

IntervalMatch(E_TERMIN)

load distinct [From Date],[To Date]

resident Excel;

but still i am getting error.

Can you please help me on this.

Thanks and Regards,

R. Bharat Kishore

Re: Link between qvd and excel file

Wed, 19 Jun 2013 06:12:48 GMT

see attached file on interval match.

Re: Link between qvd and excel file

bharatkishore — Wed, 19 Jun 2013 06:46:49 GMT

Pls find the attached file and please help me on this.

Re: Link between qvd and excel file

jagan — Wed, 19 Jun 2013 09:10:32 GMT

Hi Bharat,

Hope this script helps you for implementing the IntervalMatch. What is the common field in both the tables?

==== Load Activities ==================================================

Data:

	LOAD * INLINE [Date, Key1, Other
					2010-01-01, A, z
					2010-02-01, A, a
					2010-03-01, A, z
					2010-04-01, A, b
					2010-05-01, A, d
					2010-06-01, A, e
					2010-01-01, B, f
					2010-02-01, B, t
					2010-03-01, B, k
					2010-04-01, B, e
					2010-05-01, B, z
					2010-06-01, B, a];

// ===== Load the Contract ================================================

Contract:

	LOAD * INLINE [Cntrct_From, Cntrct_To, Key1, Val
					2010-01-01, 2010-12-31, A, 5
					2010-01-01, 2010-03-30, B, 3
					2010-04-01, 2010-12-31, B, 4];

// ==== Link Fields from Data with Contract into the Contract-Table ========

INNER JOIN INTERVALMATCH (Date, Key1) LOAD Cntrct_From, Cntrct_To, Key1 RESIDENT Contract;

// ==== Join into one Table only, remove contract-Data =====================

JOIN (Data) LOAD * RESIDENT Contract;

DROP TABLE Contract;

DROP FIELD Cntrct_From, Cntrct_To;

Regards,

Jagan.