topic Re: Interval Match Issue in QlikView

Interval Match Issue

Fri, 04 Dec 2015 17:42:19 GMT

Hi,

I need to find the number of active associates for a particular period and I am trying to achieve it using the interval match function.

This is working fine if the combination of DT_ADESAO_PERIOD,DT_CANCEL_PERIOD are distinct in the data set.

If the date periods are duplicated, i am not getting the count correctly. Given below is a sample data set contains some duplicate periods.

Can anyone please help on this?

Please find the code below.

Data:

Load * inline

[

ASSO_ID,DT_ADESAO_PERIOD,DT_CANCEL_PERIOD

1,201101,201505

2,201101,201201

3,201101,201601

4,201101,201601

5,201102,201601

6,201001,201002

7,201001,201512

8,201301,201601

9,201301,201601

10,201301,201601

11,200901,201102

];

Periods:

LOAD

201100 + RecNo() AS %Period

AUTOGENERATE

Join(Data)

IntervalMatch (%Period)

LOAD

DT_ADESAO_PERIOD,DT_CANCEL_PERIOD

RESIDENT

Data;

DROP Table Periods;

Actually associates active for the period 201101 is 6. But as per the above code, I am getting the count as 8.

the join is giving issue because of the duplicate date values for different associates.

Can anybody please advise how to handle this situation. How to make the count correct in such scenario.

How to bring the associate id also in to the condition to avoid this scenario.

Thanks

NV.

Re: Interval Match Issue

Anonymous — Fri, 04 Dec 2015 18:12:17 GMT

Would adding a Distinct suffice ?

Join(Data)

IntervalMatch (%Period)

LOAD Distinct

DT_ADESAO_PERIOD,DT_CANCEL_PERIOD

RESIDENT

Data;

Re: Interval Match Issue

sunny_talwar — Fri, 04 Dec 2015 18:15:43 GMT

I am seeing the count to be 6

Re: Interval Match Issue

Anonymous — Fri, 04 Dec 2015 18:54:42 GMT

The count is 8 though if you use %Period as the Dimension.

Re: Interval Match Issue

sunny_talwar — Fri, 04 Dec 2015 18:56:56 GMT

Got it . Was not sure which dimension to use

Re: Interval Match Issue

sunny_talwar — Fri, 04 Dec 2015 18:57:29 GMT

And adding DISTINCT did help

Re: Interval Match Issue

Anonymous — Fri, 04 Dec 2015 19:04:50 GMT

IntervalMatch() creates a synthetic key which many people don't like. But there is no need to worry about this as it is a good synthetic key as hic advises in this Blog Post, IntervalMatch

If people really cannot leave it be, as is recommended, with the synthetic key, then it needs the distinct to avoid multiple counting.

Re: Interval Match Issue

Fri, 04 Dec 2015 19:23:09 GMT

Thank you Bill.. The issue is resolved..

Appreciate your help !!

Re: Interval Match Issue

Anonymous — Fri, 04 Dec 2015 19:48:17 GMT

Give it try without the join so it leaves the synthetic key. Safer that way as there may well be other scenarios, hic‌ may be able to advise.