topic Re: Intervalmatch? in QlikView

Intervalmatch?

Thu, 06 Oct 2011 15:08:12 GMT

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!

Re: Intervalmatch?

its_anandrjs — Thu, 06 Oct 2011 15:29:41 GMT

Hi,

Make another column or add another expression like

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

in the expression

Rgds

Anand

Re: Intervalmatch?

its_anandrjs — Thu, 06 Oct 2011 15:32:46 GMT

Hi,

See the attached sample file

HTH

Let me know about this.

Rgds

Anand

Intervalmatch?

Fri, 07 Oct 2011 07:17:40 GMT

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

Intervalmatch?

its_anandrjs — Fri, 07 Oct 2011 07:34:08 GMT

Hi,

Just add another expression

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

to check parallel activity

Rgds

Anand

Intervalmatch?

kaushiknsolanki — Fri, 07 Oct 2011 07:35:50 GMT

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

Intervalmatch?

Fri, 07 Oct 2011 07:47:01 GMT

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é

Intervalmatch?

its_anandrjs — Fri, 07 Oct 2011 07:52:32 GMT

Hi esmeschelling,

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

Rgds

Anand