Interval Match Issue

[#1]

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

1;

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.

[Anonymous · Accepted solution]

Would adding a Distinct suffice ?

Join(Data)

IntervalMatch (%Period)

LOAD Distinct

DT_ADESAO_PERIOD,DT_CANCEL_PERIOD

RESIDENT

Data;

[sunny_talwar]

I am seeing the count to be 6

[Anonymous]

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

[sunny_talwar]

Got it . Was not sure which dimension to use

[sunny_talwar]

And adding DISTINCT did help

[Anonymous]

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.

[#8]

Thank you Bill.. The issue is resolved..

Appreciate your help !!

[Anonymous]

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.