Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Intervalmatch?

Hi,

I'm trying to check whether a certain start date lies within a period of any other record in the table. Customers can buy different product form different department with different start and end dates. I would like to determine whether the start date of a product is overlapping with an existing product from that department.

For example:

CustID, ProductID, Department, StartDate, EndDate

1,38495,CHI, 20091201,20100105

1,38496,CHI, 20100131,20100431

2,45285,ORT,20080930,20081201

2,45275,ORT,20081005,20090125

2,58451,KNO,20081108,20090208

As you can see the second product of customer 2 at the ORT department started at 20081005 while the first product was still open. I would like to add an extra column which tells me for every product if it started while an other was still open, like this:

CustID, ProductID, Department, StartDate, EndDate, Parallel

1,38495,CHI, 20091201,20100105,0

1,38496,CHI, 20100131,20100431,0

2,45285,ORT,20080930,20081201,0

2,45275,ORT,20081005,20090125,1

2,58451,KNO,20081108,20090208,0

How can I make this work?

Thank you in advance!

1 Solution

Accepted Solutions
its_anandrjs

Hi,

Make another column or add another expression like

If( StartDate < Above(EndDate) , 1,0)

in the expression

Rgds

Anand

View solution in original post

7 Replies
its_anandrjs

Hi,

Make another column or add another expression like

If( StartDate < Above(EndDate) , 1,0)

in the expression

Rgds

Anand

its_anandrjs

Hi,

See the attached sample file

HTH

Let me know about this.

Rgds

Anand

Not applicable
Author

Hi Anand,

Thank you for the answer! Sorry, I can not open the attachment: Personal Edition. Can you post it in the text field?!

Rgds,

Esme

its_anandrjs

Hi,

Just add another expression

If( StartDate < Above(EndDate) , 1,0)

to check parallel activity

Rgds

Anand

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

    Anand has created a straight chart with dimensions as CustID,ProductID and Dept.ID and expression as follows.

    1. StartDate,

    2. EndDate

    3. If( StartDate < Above(EndDate) , 1,0)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi,

Thank you for your help. It works great!

Only a problem appeared when the product above is parallel, but not within the same department or for the same customer. Therefor I added a few extra conditions:

if(CustID=Above(CustID) and Depart=Above(Depart) and

StartDate < Above(Enddate), 'parallel','not-parallel')

Regards,

Esmé

its_anandrjs

Hi esmeschelling,

Thanks i am glad to help you and thanks kaushik to explaining the objective

Rgds

Anand