topic Re: IntervalMatch on multiple fields in a table in QlikView

IntervalMatch on multiple fields in a table

Mon, 03 Aug 2015 22:40:25 GMT

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

Re: IntervalMatch on multiple fields in a table

swuehl — Mon, 03 Aug 2015 22:52:35 GMT

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.

Re: IntervalMatch on multiple fields in a table

Tue, 04 Aug 2015 11:47:03 GMT

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.

Re: IntervalMatch on multiple fields in a table

swuehl — Tue, 04 Aug 2015 12:03:16 GMT

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.

Re: IntervalMatch on multiple fields in a table

Tue, 04 Aug 2015 12:40:08 GMT

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.

Re: IntervalMatch on multiple fields in a table

swuehl — Tue, 04 Aug 2015 16:23:51 GMT

Attached what I was thinking about.

Re: IntervalMatch on multiple fields in a table

Tue, 04 Aug 2015 22:09:19 GMT

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?

Re: IntervalMatch on multiple fields in a table

swuehl — Tue, 04 Aug 2015 22:35:12 GMT

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.

Re: IntervalMatch on multiple fields in a table

Wed, 05 Aug 2015 14:03:22 GMT

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!