11 Replies Latest reply: Jan 9, 2014 5:01 PM by Steve Zagzebski

# Interval Match Problem

I have tried looking at all the discussions on this site but still can't get what I want! I keep getting results different that what I exopect and with a sythnetic table. Seems basic all I want is ONE table with the following results...

 Entity ServiceDate Ownership_Pct Effective_YearMth Expiration_YearMth DEAN 200901 0.2 200801 999912 DEAN 200701 0.1 200701 200712 DNON 201001 0.6 200901 999912

Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712,  0.10
DEAN, 200801, 999912,  0.20
DNON, 200001, 200812,  0.50
DNON, 200901, 999912,  0.60
]
;

FACT:
Entity, ServiceDate
DEAN, 200901
DEAN, 200701
DNON, 201001]
;

Left Join(FACT)
IntervalMatch([ServiceDate],Entity)

Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;

• ###### Re: Interval Match Problem

I believe, In your requirement, IntervelMatch won't fix the problem because both tables having Entity.

TEMP:

Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct

DEAN, 200701, 200712,  0.10

DEAN, 200801, 999912,  0.20

DNON, 200001, 200812,  0.50

DNON, 200901, 999912,  0.60

];

Left Join

Entity, ServiceDate

DEAN, 200901

DEAN, 200701

DNON, 201001];

FINAL:

LOAD * , IF(ServiceDate >= Effective_YearMth AND ServiceDate <= Expiration_YearMth , 1,0) AS FLAG

Resident TEMP;

DROP Table TEMP;

DROP Field FLAG ;

• ###### Re: Interval Match Problem

Thanks for the response. Unfortunately I am not sure this will work. In my FACT table (from above) there is a chance there will be a record that does not join to the Owner Def table so it will get dropped because it is on the right side of the left join.

• ###### Re: Interval Match Problem

I would not join the tables - but you can if you know that there is a one-to-one relationship between the dates and the intervals. I would instead go for the following model:

HIC

• ###### Re: Interval Match Problem

Henric -

Thanks for the response. Is there any way to just get all the data into the fact table. and not have to deal with a bridge table. we have quite a large model and I am hoping just to bring all the data into the FACT table.

Steve

• ###### Re: Re: Interval Match Problem

No problem. Check attached script.

HIC

• ###### Re: Re: Interval Match Problem

Thanks again.

Getting close. However the resulting table is the Owner Def table. I would lose my FACT table - which is the main table in our model. The Owner Def table is just an attributes table that can/should go away after using it in the interval match..

Any thoughts?

Steve

• ###### Re: Interval Match Problem

Just change the Join to FACT table like below:

OWNER_DEF:

Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct

DEAN, 200701, 200712,  0.10

DEAN, 200801, 999912,  0.20

DNON, 200001, 200812,  0.50

DNON, 200901, 999912,  0.60

];

FACT:

Entity, ServiceDate

DEAN, 200901

DEAN, 200701

DNON, 201001];

Join (FACT)

IntervalMatch([ServiceDate],Entity)

Effective_YearMth,

Expiration_YearMth,

Entity

Resident OWNER_DEF;

Left Join (FACT) // Left Join Keep only all records in FACT Tabel

Drop Table OWNER_DEF;

• ###### Re: Interval Match Problem

No, the table is the join between the tables, so it contains all information from both tables. Yes, it is labeled OWNER_DEF, but this is just a matter of labeling. To change the join order - so that you start with the joining to the FACT table - does not change anything.

HIC

• ###### Re: Interval Match Problem

Okay I think I have it finally! My last question is that an extra row is created in the resulting table (bolded record with DNON and no service date or area). This record was not from the FACT table  but got created through the join process. Any way to only have the FACT table records records?

The resulting table is (updated the code below slighlty). :

 Entity ServiceDate Ownership_Pct Effective_YearMth Expiration_YearMth Area DEAN 200901 0.2 200801 999912 North DEAN 200701 0.1 200701 200712 South DNON 201001 0.6 200901 999912 East DNON 0.5 200001 200812 TEST 201001 West

Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712,  0.10
DEAN, 200801, 999912,  0.20
DNON, 200001, 200812,  0.50
DNON, 200901, 999912,  0.60
]
;

FACT:
Entity, ServiceDate, Area
DEAN, 200901, North
DEAN, 200701, South
DNON, 201001, East
TEST, 201001, West]
;

Join (OWNER_DEF)
IntervalMatch([ServiceDate],Entity)

Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;

Join (OWNER_DEF)

Drop Table FACT;

• ###### Re: Interval Match Problem

Yes. Change the last Join to

Right Join (OWNER_DEF)

• ###### Re: Interval Match Problem

It works perfect! Thanks so much for sticking with me on this.