Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IntervalMatch on multiple fields in a table

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?????

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Attached what I was thinking about.

View solution in original post

8 Replies
swuehl
MVP
MVP

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.

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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
MVP
MVP

Attached what I was thinking about.

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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!