Should I use Intervalmatch

[jcampbell474]

I have a table with three fields.  Store #, open date, and close date.  I have another table with dates.  Dates are in the format 201601, 201602, etc... 

I need to create a column in the first table that indicates if the store is open or closed, for all of the open and close dates in the other table.  They can have records after they've closed, so new records indicating the store is closed will not be necessary.  I've been reading up on Intervalmatch, but never used it and not sure how to apply it.  If it is, how should I apply it?  If not, is there a better method??

Thanks in advance! 

[Clever_Anjos]

Left Join (Data) IntervalMatch(MonthTimeKey,AgtID) // Date is related to which Open/Close Date?

LOAD

OpenDateYrMth,

CloseDateYrMth,

AgtID

Resident NewDate;

[jcampbell474]

Thanks, Sunny and Clever.  That did the trick - no more error!  I spent a couple of hours troubleshooting it, only to find out it was the order of the fields.  I reckon 🙂

So now it doesn't produce any errors, but it never finishes loading.  I'm loading a little more than 22-million records. All of the data loads in 00:01:21, then it just hangs.  Memory on the server steadily increases.  It just climbed to ~90gb, so I killed the process.

I thought interval match would be the most efficient approach from a system resource perspective.  Could the increased resource usage be due to how I have it integrated into my model?  Should I try a different method?

Thanks!

[sunny_talwar]

Jason -

Why don't you not join the Interval Match table? As per HIC the synethic key that is created by keeping the Interval Match table separate isn't a bad thing. Read here: IntervalMatch‌

Extract from the above link:

Not sure how you are using the Flag, but I guess you won't able to create that flag unless you have them in one table. May be use ApplyMap or Lookup to do that.

[jcampbell474]

Thank you, Sunny.  Henric's post initially made me think that IntervalMatch is what I need to use.  I just couldn't figure out how to implement it.  Here's my script:

Data2:

Load  

AgtID,  

MonthTimeKey as Datekey

Resident Data;  

Intervals:

LOAD * INLINE [

    From, To, Status

    198001, 201608, Active];

IntervalMatch:

IntervalMatch (Datekey)

Load Distinct

From,

To

Resident Intervals;

Do I need need to create each interval in the Intervals table?

Thanks!

[sunny_talwar · Accepted solution]

What you have above looks right, are you running into errors?

[jcampbell474]

Not getting any errors - just takes awhile to load/process, even with a limited load of 5000 records.  I go to validate the results and the closed date status isn't correct.

Looks like it works for one date.  I.e.,  Active date.  Not sure how to look at both the Open and Closed date.  (Add a record to the Intervals table for InActive?)