Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
qlikdash
Valued Contributor

How to create a filter for OVERLAPPING DATES

I have the following fields:

ProductID, Contract#, Contract status, Contract start date & contract end date. contracts are assigned only after one expires. Meaning, contracts should be extended only after the 1st one expires. So I am looking for duplicate contracts that were issued before the previous one expired and it can be captured by spotting overlapping dates. I was thinking about Interval match but I dont know where to start. any help would be great I have also attached excel sheet.

Many Thanks

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: How to create a filter for OVERLAPPING DATES

Hi,

You could check for overlapping in the script.

temp_contr:

LOAD ProductID,

     [Contract #],

     [Contract Status],

     Date#([Contract Start Date],'M/D/YYYY') As [Contract Start Date],

     Date#([Contract End Date] ,'M/D/YYYY') As [Contract End Date]

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

temp2_contr:

LOAD

  ProductID,

     [Contract #],

     [Contract Status],

     [Contract Start Date],

     [Contract End Date]

RESIDENT temp_contr order by ProductID, [Contract Start Date], [Contract End Date];

contracts:

LOAD

  ProductID,

     [Contract #],

     [Contract Status],

     [Contract Start Date],

     [Contract End Date],

     If(ProductID = Peek(ProductID),If([Contract Start Date] < Peek([Contract End Date]), 1, 0),0) As Overlapping

RESIDENT temp2_contr;

Drop Tables temp_contr, temp2_contr;

View solution in original post

6 Replies
Highlighted
Partner
Partner

Re: How to create a filter for OVERLAPPING DATES

I think you can use canonical date for this.

Please check this link : Canonical Date

Highlighted
qlikdash
Valued Contributor

Re: How to create a filter for OVERLAPPING DATES

My Expn is Count(distinct SID) other columns r just Dimensions

Highlighted
Not applicable

Re: How to create a filter for OVERLAPPING DATES

Hi,

You could check for overlapping in the script.

temp_contr:

LOAD ProductID,

     [Contract #],

     [Contract Status],

     Date#([Contract Start Date],'M/D/YYYY') As [Contract Start Date],

     Date#([Contract End Date] ,'M/D/YYYY') As [Contract End Date]

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

temp2_contr:

LOAD

  ProductID,

     [Contract #],

     [Contract Status],

     [Contract Start Date],

     [Contract End Date]

RESIDENT temp_contr order by ProductID, [Contract Start Date], [Contract End Date];

contracts:

LOAD

  ProductID,

     [Contract #],

     [Contract Status],

     [Contract Start Date],

     [Contract End Date],

     If(ProductID = Peek(ProductID),If([Contract Start Date] < Peek([Contract End Date]), 1, 0),0) As Overlapping

RESIDENT temp2_contr;

Drop Tables temp_contr, temp2_contr;

View solution in original post

Highlighted
Not applicable

Re: How to create a filter for OVERLAPPING DATES

I just realized you could miss some lines by the method I suggested.

Maybe add another table with distinct ProductID and check for overlapping:

overlap:

LOAD DISTINCT

  ProductID,

  If(Sum(Overlapping) > 0, 1, 0) As check

Resident contracts Group by ProductID;

Highlighted
qlikdash
Valued Contributor

Re: How to create a filter for OVERLAPPING DATES

Thanks t h , I will use this approach and let you know how it goes

Highlighted
Not applicable

Re: How to create a filter for OVERLAPPING DATES

Hi,

Is there any way to see all the overlapping contract ids for a particular product id instead of  showing that overlapped contract id alone in the above script?

eg:

product-id, contract-id  ,start-date ,end-date

p1, c1, 25/1/15, 29/1/15

p1, c2, 26/1/15, 25/2/16

p1, c3,23/2/16, 25/5/17

Here I would like to see all the contract ids as overlapped instead of c2 and c3.

Thanks,

Kiruthi