Announcements
cancel
Showing results for
Did you mean:
Creator II

What is interval match.

Hi All,

What is interval match.Please give an example.

1 Solution

Accepted Solutions
Master II

Hi Check this also.

Data:

LOAD * INLINE [Date, Key, Desc

2010-01-01, A, z1v

2010-02-01, A, a1v

2010-03-01, A, z1v

2010-04-01, A, b1v

2010-05-01, A, d1v

2010-06-01, A, e1v

2010-01-01, B, f1v

2010-02-01, B, t1v

2010-03-01, B, k1v

2010-04-01, B, e1v

2010-05-01, B, z1v

2010-06-01, B, a1v];

// ===== Load the Contract ================================================

Contract:

LOAD * INLINE [START_DATE, END_DATE, Key, Value

2010-01-01, 2010-12-31, A, 5

2010-01-01, 2010-03-30, B, 3

2010-04-01, 2010-12-31, B, 4];

// ==== Link Fields from Data with Contract into the Contract-Table ========

INNER JOIN INTERVALMATCH (Date, Key) LOAD START_DATE, END_DATE, Key RESIDENT Contract;

// ==== Join into one Table only, remove contract-Data =====================

JOIN (Data) LOAD * RESIDENT Contract;

DROP TABLE Contract;

DROP FIELD START_DATE, END_DATE;

4 Replies
MVP
MVP

IntervalMatch

Master

The Interval Match is one of the functions in QlikView that can be used to match a numeric value in a single table, such as date, timestamp, score, percent or any number, to an interval or duration in a second table. The IntervalMatch function discreet data to one or more dimensions that are changing over time. It can be us for resolving slowly changeing dimensions by linking the specific key fields to the appropriate numeric intervals. It can be used with LOAD, Inline and SQL select statements.

Check these:

Example:

EventLog:

Time, Event, Comment

00:00, 0, Start of shift 1

01:18, 1, Line stop

02:23, 2, Line restart 50%

04:15, 3, Line speed 100%

08:00, 4, Start of shift 2

11:43, 5, End of production

];

OrderLog:

Start, End, Order

01:00, 03:35, A

02:30, 07:58, B

03:04, 10:27, C

07:23, 11:43, D

];

//Link the field Time to the time intervals defined by the fields Start and End.

Inner Join IntervalMatch ( Time )

Resident OrderLog;

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

Master II

Hi Check this also.

Data:

LOAD * INLINE [Date, Key, Desc

2010-01-01, A, z1v

2010-02-01, A, a1v

2010-03-01, A, z1v

2010-04-01, A, b1v

2010-05-01, A, d1v

2010-06-01, A, e1v

2010-01-01, B, f1v

2010-02-01, B, t1v

2010-03-01, B, k1v

2010-04-01, B, e1v

2010-05-01, B, z1v

2010-06-01, B, a1v];

// ===== Load the Contract ================================================

Contract:

LOAD * INLINE [START_DATE, END_DATE, Key, Value

2010-01-01, 2010-12-31, A, 5

2010-01-01, 2010-03-30, B, 3

2010-04-01, 2010-12-31, B, 4];

// ==== Link Fields from Data with Contract into the Contract-Table ========

INNER JOIN INTERVALMATCH (Date, Key) LOAD START_DATE, END_DATE, Key RESIDENT Contract;

// ==== Join into one Table only, remove contract-Data =====================

JOIN (Data) LOAD * RESIDENT Contract;

DROP TABLE Contract;

DROP FIELD START_DATE, END_DATE;

Community Browser