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! 

[sunny_talwar]

IntervalMatch can be used or you can use While statement to create a single date from open and close date. Both options are resource extensive, but can work in this situation

[Clever_Anjos]

You can use IntervalMatch

I´ve created a script, so you can study a possible technique

OpeningGrid: // Generating a list of stores with open/close dates
LOAD
  Store,
  OpenDate,
  Date(OpenDate + Floor(Rand()*10)) as CloseDate;
LOAD
  Pick(1+Mod(RecNo(),3),'A','B','C') as Store,
  Date(Floor(Today() - Rand()*100)) as OpenDate
AutoGenerate 50;

OtherTable: // your "another table"
LOAD
  Pick(1+Mod(RecNo(),3),'A','B','C') as Store,
  Date(Floor(Today() - Rand()*100)) as Date
AutoGenerate 50;

Left Join(OtherTable) IntervalMatch(Date,Store) // Date is related to which Open/Close Date?
LOAD
  OpenDate,
  CloseDate,
  Store
Resident OpeningGrid;
Drop Table OpeningGrid;
Left Join(OtherTable) // Calculating a flag to know if the store was open
Load
  Store,
  Date,
  If(IsNull(OpenDate),'N','Y') as [Flag Open]
Resident OtherTable;

[jcampbell474]

Sunny and Clever, I really appreciate your assistance. 

Clever, thank you for the script - I'm just not sure how to use it with my resident tables.  Upon further inspection, I think my scenario description is different.  I have a Fact table and a Store table.  Joined by ID.  The Fact table has MonthTimeKey (201601, 201602, etc...) for the transaction date.  The Store table has OpenDate and CloseDate. 

How can I create a column in the Fact table that indicates if the store was open or closed at the time of the transaction?

Something like:

if(MonthTimeKey >= OpenDate and Len(CloseDate) < 1, 'Open', if(MonthTimeKey >= OpenDate and MonthTimeKey <= CloseDate,'Open','Closed')) as StoreStatus  //If a close date is not present, the store is currently open.

Again, I really appreciate your help.  My apologies for the inaccurate description.

[Clever_Anjos]

Can you post a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

[Clever_Anjos]

Another doubt...

MonthTimeKey is a month right?

Let´s get the 201601 month.

What if the store opened on Jan/05th and closed Jan/10th?

It must counted as opened or not-opened?

[jcampbell474]

Clever, thank you.  The app is loaded via Binary with many tables, so it would be quite a chore to make it a manageable size.  So, I created an app that I think represents the issue(s) I'm having.  It is attached.

Objective: Using the MonthTimeKey of the transaction in the Store table, list in a column if the Store was Open or Closed during the month of the transaction.  The Date table lists Stores and their Open/Closed dates.

Thanks you again!

[Clever_Anjos]

Please try this after at the end of your script;

NewDate:
NoConcatenate LOAD
  Store#,
  OpenDate,
  Alt(CloseDate,Year(Today())*100+Month(Today())) as CloseDate
Resident Date;
Drop Table Date;

Left Join(Store) IntervalMatch(MonthTimeKey,Store#) // Date is related to which Open/Close Date? 
LOAD 
  OpenDate, 
  CloseDate, 
  Store# 
Resident NewDate;
Drop Table NewDate;
 Left Join(Store) // Calculating a flag to know if the store was open 
Load 
  Store#, 
  MonthTimeKey, 
  If(IsNull(OpenDate),'N','Y') as [Flag Open] 
Resident Store; 

[jcampbell474]

Clever, to answer your question.  If a store opened on 1/5 and closed on 1/10...it would be considered as Open.

Now, to the script.  The example works perfect, but I can't get it to work in my test app.  Here is the script I'm using (w/TestFieldNames).  I'm getting an error saying that OpenDateYrMth can't be found (in the Flag Open expression).  I can't figure out why...

NewDate:

NoConcatenate LOAD

AgtID,

OpenDateYrMth,

CloseDateYrMth

Resident AgentLookup;

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

LOAD

AgtID,

OpenDateYrMth,

CloseDateYrMth

Resident NewDate;

Drop Table NewDate;

Left Join(Data) // Calculating a flag to know if the store was open

Load

AgtID,

MonthTimeKey,

If(IsNull(OpenDateYrMth),'N','Y') as [Flag Open]

Resident Data;

Thank you again for the help!

[sunny_talwar]

I think you got the order of AgtID wrong in the intervalMatch load. AgtID cannot come before the two dates:

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

LOAD OpenDateYrMth,

          CloseDateYrMth,

          AgtID

Resident NewDate;