IntervalMatch on multiple fields in a table

[#1]

How do I do an Intervalmatch on multiple fields in one single Fact table.  Using the example below, I want to do an intervalmatch on the fields not only on Date but also on Date2, Date3, etc.   Is there another solution besides using IntervalMatch?

Example:

LOAD * INLINE
[ 
     Name, Start, End 
     First,     01/01/2010, 31/01/2010  
     Second,    01/02/2010, 28/02/2010
     Third,     01/03/2010, 31/03/2010
     Four,      01/04/2010, 30/04/2010
     Five,      01/05/2010, 30/05/2010
     Six,       01/06/2010, 31/06/2010
     Seven      01/07/2010, 30/07/2010
];  


FactsTable:
LOAD * INLINE
[ 

     ID,  Date,            Date2,          Date3,                    Amount 

     A,   03/02/2010,    15/06/2010      03/05/2010                100 
     B,   16/01/2010,    03/04/2010      22/03/2010                200
     C,   15/03/2010,    20/07/2010      15/05/2010                300
     E,   15/06/2010,    10/01/2010      12/06/2010                600

];  

FactsDates: 
INTERVALMATCH (Date) LEFT JOIN LOAD Start, End RESIDENT DatesTable; 
LEFT Join (FactsTable) LOAD * RESIDENT DatesTable;  

 
DROP TABLE DatesTable;

How to do an IntervalMatch on Date2, Date3, as well?????

[swuehl]

You may consider transforming your facts table using e.g. CROSSTABLE LOAD prefix into a ID, Date, DateType structure. Then do the IntervalMatch on Date field.

[#3]

Thank you.  I am not sure this will help or I understand completely because I need to do an intervalmatch on 3 fields Date, Date2, and Date3.

[swuehl]

The CROSSTABLE LOAD will transform Date1, Date2 and Date3 field to a common Date field (which you can IntervalMatch then) and DateType (labelling the Date field as of type Date1, Date2, Date2).

Basically, you transform 1 records with 3 fields into 3 records with two fields.

The benefit is the common Date field.

See also

Canonical Date

Alternatively, you can indeed intervalmatch the three fields on their own, but I think you will not get a common interval field matched to the fields (and I assume that's what you want).

Maybe I just don't get what you really want. If possible, prepare a small sample QVW with your data model and attach your requested outcome.

[#5]

I  attached a sample .QVW in my initial discussion.  Basically, I need to do an intervalmatch on Marks_Qtr1, Marks_Qtr2, and Marks_Qtr3. So  I need to know if a student pass, failed, etc for EACH quarter.  I hope this makes sense.  As of right now, it looks like I can only do an intervalmatch on Marks_Qtr1.

[swuehl · Accepted solution]

Attached what I was thinking about.

[#7]

This solution may work!  The problem I am having now is some of the Mark_Qtr values are null or blank.  I want to display when Mark Qtr values are null or blank to display the interval name 'No data available' in the Grade table. Any suggestions?

[swuehl]

You can assign the missing marks a value that is outside of your marks range, like zero (or -1, etc.).

And add an appropriate interval to your range table.

See attached.

You may need to filter this range class when calculating avg scores etc.

[#9]

Since the QVW I provided is not the real business case scenario, I just used this Marks sample QVW as a case similar to my real business case.  My values actually fall into the - and 0 range so there is likelyhood that I would use the - and 0 intervals.  For now, I just used -50000 for null values in hopes my values never fall into this range!