IntervalMatch with different dates and conditions

terezagr — Wed, 08 Apr 2015 15:24:58 GMT

Hi,

I have the following tables:

Fact_Table:

CAP_SK	ASC	App	Dec	Final_Dec	INTERVAL_START	INTERVAL_END
212468	_	UOOO	1UN	U	679186	679278
212468	_	UOOO	1UAF	UF	679279	679643
212468	_	UOOO	1UAF	UF	679644	679711
212468	DT	UOOO	1UAF	UF	679712	679713
212468	DT	UOOO	1UAF	UF	679714	678968

Calendar:

Hour_ID	Date	Hour
679186	25/10/2012 15:00	15
679279	29/10/2012 12:00	12
679644	13/11/2012 17:00	17
679712	16/11/2012 13:00	13
679714	16/11/2012 15:00	15
679278	29/10/2012 11:00	11
679643	13/11/2012 16:00	16
679711	16/11/2012 12:00	12
679713	16/11/2012 14:00	14
678968	16/10/2012 13:00	13

I am performing an intervalMatch as follow:

FACT_TABLE:

LOAD

CAP_SK,

ASC,

App,

Dec,

Final_Dec,

INTERVAL_START,

INTERVAL_END

FROM FACT_TABLE.qvd(qvd);

CALENDAR:

LOAD

Hour_ID,

Date,

Hour

FROM Calendar.qvd(QVD);

IntervalMatch_FACT_TABLE:

IntervalMatch(Hour_ID)

LOAD

INTERVAL_START,

INTERVAL_END

Resident FACT_TABLE;

This creates an Interval Match with no problem. My problem after that is in the front-end of my application.

Example:

I want to see all applications between 25/10/2012 (INTERVAL_START) and 16/11/2012 (INTERVAL_END).

As you can see above in the table Fact_Table, there should be 5 applications listed when I pick these two dates. What I end up having when I pick these two dates is just 1 application - and this relates to 25/10/2012 (INTERVAL_START). INTERVAL_END seems not to be taken into consideration.

Is there any way how to do this in front end by using set analysis?

Re: IntervalMatch with different dates and conditions

ramoncova06 — Wed, 08 Apr 2015 15:49:28 GMT

check this post

QlikView App: Dates, Date Ranges and Set Analysis

Re: IntervalMatch with different dates and conditions

terezagr — Wed, 22 Apr 2015 10:27:48 GMT

Thanks Ramon!

In the end I have used the following expressions and it worked

For Interval Start dates:

=(DATE(min({<ACT_DATE={">=$(INTERVAL_END)"}>}DATE)))

For Interval End dates:

DATE(max({<ACT_DATE={"<=$(INTERVAL_START)"}>}DATE))

topic Re: IntervalMatch with different dates and conditions in QlikView

IntervalMatch with different dates and conditions

Re: IntervalMatch with different dates and conditions

Re: IntervalMatch with different dates and conditions