Link between qvd and excel file

[bharatkishore]

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

[Gysbert_Wassenaar]

Read this blog post: IntervalMatch

[bharatkishore]

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

[#4]

see attached file on interval match.

[bharatkishore]

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

[jagan]

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.