Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Interval match and Peek function.

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 centerStart dateend datecapacity
AA1/1/20141/12/2014100
AA1/14/20141/24/2014200
BB1/8/20141/17/2014100
BB1/19/20141/24/2014200

                         table: 1

                          

Work centerDate
AA1/10/2014
AA1/16/2014
AA1/19/2014
BB1/10/2014
BB1/16/2014
BB1/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 centerStart datecapacity
AA1/1/2014100
AA1/22/2014200
BB1/8/2014100
BB1/25/2014200

the output table should be like

Work centerStart dateLast dateCapacity
AA1/1/20141/21/2014100
AA1/22/20141/22/2014200
BB1/8/20141/24/2014100
BB1/25/20141/25/2014200

vinaybangari

Your assistance will be highly appreciated.

1 Solution

Accepted Solutions
vinay_bangari
Valued Contributor III

Re: Interval match and Peek function.

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 ;

2 Replies
Not applicable

Re: Interval match and Peek function.

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

vinay_bangari
Valued Contributor III

Re: Interval match and Peek function.

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 ;