Interval match and Peek function.

[#1]

Hi all,

I need assistance with interval match and peek function.

Given below are table 1 & table 2 we have four fields in table 1 and the requirement is to insert the date

field of table 2 with the start date & end date fields of table 1 using interval match such that you

join the two tables and the output table should contain the fields work center, date , capacity where only unique

records of work center without duplication.

Work center	Start date	end date	capacity
AA	1/1/2014	1/12/2014	100
AA	1/14/2014	1/24/2014	200
BB	1/8/2014	1/17/2014	100
BB	1/19/2014	1/24/2014	200

                         table: 1

Work center	Date
AA	1/10/2014
AA	1/16/2014
AA	1/19/2014
BB	1/10/2014
BB	1/16/2014
BB	1/19/2014

          table:2

Other requirement is from the table below, i need to get the last working date of the work center from start date by comparing the next record using interval match and peek function.

Work center	Start date	capacity
AA	1/1/2014	100
AA	1/22/2014	200
BB	1/8/2014	100
BB	1/25/2014	200

the output table should be like

Work center	Start date	Last date	Capacity
AA	1/1/2014	1/21/2014	100
AA	1/22/2014	1/22/2014	200
BB	1/8/2014	1/24/2014	100
BB	1/25/2014	1/25/2014	200

vinaybangari

Your assistance will be highly appreciated.

[#2]

Hi Abhinav,

I worked out solution for your problem. Please see the attached .qvw. Please mark it as answered to close this thread if you find the solution helpful.

Regards

[Anonymous · Accepted solution]

please use below script and change the path of the excel sheets that should solve your issue:

Final:

LOAD [Work center],

    Date

FROM

(ooxml, embedded labels, table is Sheet1);

Temp_Capacity:

LOAD [Work center],

    [Start date],

    capacity

FROM

(ooxml, embedded labels, table is Sheet2);

//

//

Cap2:

LOAD

    [Start date],

    if(len(Peek([Start date]))=0 or [Work center]<>peek([Work center]),'1/1/2020',date(peek([Start date])-1)) as End_date,

    [Work center],

    capacity

Resident Temp_Capacity

order by [Work center], [Start date]  desc;

drop table Temp_Capacity;

left join (Final)

IntervalMatch(Date,[Work center])

LOAD

    [Start date],

      End_date,

    [Work center]

Resident Cap2 ;