topic Re: How to create a filter for OVERLAPPING DATES in QlikView

How to create a filter for OVERLAPPING DATES

Anonymous — Fri, 27 Mar 2015 23:41:10 GMT

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

Re: How to create a filter for OVERLAPPING DATES

simsondevadoss — Sat, 28 Mar 2015 12:23:02 GMT

I think you can use canonical date for this.

Please check this link : Canonical Date

Re: How to create a filter for OVERLAPPING DATES

Anonymous — Sat, 28 Mar 2015 23:55:07 GMT

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

Re: How to create a filter for OVERLAPPING DATES

Sun, 29 Mar 2015 14:43:20 GMT

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;

Re: How to create a filter for OVERLAPPING DATES

Sun, 29 Mar 2015 15:02:44 GMT

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;

Re: How to create a filter for OVERLAPPING DATES

Anonymous — Sun, 29 Mar 2015 23:31:02 GMT

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

Re: How to create a filter for OVERLAPPING DATES

Sun, 20 Sep 2015 01:50:48 GMT

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