topic Re: IntervalMatch for two different ranges in QlikView

IntervalMatch for two different ranges

fasibaig — Tue, 28 Oct 2014 16:05:52 GMT

Any Thoughts Please.

I would like to use IntervalMatch by satisfying two conditions like

((SEGMENT2 >= '001' AND SEGMENT2 <= '399') AND

(SEGMENT3 >= '5650' AND SEGMENT3 <= '5670'))

When Interval match is used ONLY for segment3 it works fine (Fig1) but

When I use Interval Match to work for segment2 and segment3 against the GL_BALANCES table it ends up in a circular reference. (Fig2)

Please find the code , diagrams, qvw file for reference

//-Dimensions
SEGMENT1:
LOAD SEGMENT1,
SEGMENT1_DESCRIPTION
FROM [temp_gl_balances.xls]
(biff, embedded labels, table is Segment1$);

SEGMENT2:
LOAD SEGMENT2,
SEGMENT2_DESCRIPTION
FROM [temp_gl_balances.xls]
(biff, embedded labels, table is Segment2$);

SEGMENT3:
LOAD SEGMENT3,
SEGMENT3_DESCRIPTION
FROM [temp_gl_balances.xls]
(biff, embedded labels, table is Segment3$);

HEADERS:
LOAD HEADER_ID,
HEADER_NAME,
CONDITION
FROM [temp_gl_balances.xls]
(biff, embedded labels, table is Headers$);

//--Facts
GL_BALANCES:
LOAD PERIOD,
CCID,
SEGMENT1,
SEGMENT2,
SEGMENT3,
NET_AMT
FROM [temp_gl_balances.xls]
(biff, embedded labels, table is GLBalances$);

//--SEGMENT Intervals
SEGMENT2_RANGE:
LOAD //HEADER_ID, ///-----NOTE NOTE NOTE--- UNCOMMENTING THIS RESULTS IN A LOOP
FROM_SEGMENT2,
TO_SEGMENT2
FROM
[temp_gl_balances.xls]
(biff, embedded labels, table is Segment2_Ranges$);

SEGMENT3_RANGE:
LOAD HEADER_ID,
FROM_SEGMENT3,
TO_SEGMENT3
FROM
[temp_gl_balances.xls]
(biff, embedded labels, table is Segment3_Ranges$);

//---Interval Match
SEGMENT2_INTERVAL_TAB:
IntervalMatch ( SEGMENT2 )
LOAD FROM_SEGMENT2, TO_SEGMENT2
RESIDENT SEGMENT2_RANGE;

SEGMENT3_INTERVAL_TAB:
IntervalMatch ( SEGMENT3 )
LOAD FROM_SEGMENT3, TO_SEGMENT3
RESIDENT SEGMENT3_RANGE;

Figure - 1

How to Avoid this loop (Figure -2 )

Thanks & Regards

Ishaq

Re: IntervalMatch for two different ranges

Anonymous — Tue, 28 Oct 2014 17:14:23 GMT

You need to concatenate your fact tables into a single fact table before doing the intervalmatch().

Re: IntervalMatch for two different ranges

fasibaig — Tue, 28 Oct 2014 17:47:32 GMT

Dear Bill,

Thanks for the suggestion. There is only one Fact GL_BALANCES and 3 Dimensions,

Segment2 and Segment3 Use the Interval Match Tables.

IntervalMatch works fine if ONLY Segment3 is used independently.

Any ideas how to include the segment2 to satisfy the condition via the IntervalMatch Tables

Example Condition -

((SEGMENT2 >= '001' AND SEGMENT2 <= '399') AND

(SEGMENT3 >= '5650' AND SEGMENT3 <= '5670'))

Thanks & Regards

Ishaq

Re: Re: IntervalMatch for two different ranges

Anonymous — Tue, 28 Oct 2014 18:01:36 GMT

Or maybe concatenate something like the attached :

Re: IntervalMatch for two different ranges

fasibaig — Tue, 28 Oct 2014 21:17:16 GMT

The Loop looks resolved but Data does not appear.

Re: IntervalMatch for two different ranges

fasibaig — Wed, 29 Oct 2014 13:25:54 GMT

Thanks to Bill for his suggestions.

Issue Resolved using the idea suggested in this link

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Diagram after Solving the Problem

Attaching the Solved .qvw file

Thanks & Regards

Ishaq