Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

using if,match,dual interval match

hi all, i am new here

can someone please ,explain how to use if ,match,dual interval match

i

9 Replies
Highlighted
Specialist
Specialist

The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals.

It must be placed before a Load or Select (SQL)statement that loads the intervals. The field containing the discrete data points (Time in the example below) must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix does not by itself read this field from the database table. The prefix transforms the loaded table of intervals to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point and interval.

The intervals may be overlapping and the discrete values will be linked to all matching intervals.

The general syntax is:

intervalmatch (matchfield) (loadstatement | selectstatement )

matchfield is the field containing the discrete numeric values to be linked to intervals.

loadstatement or selectstatement must result in a two-column table, where the first field contains the lower limit of each interval and the second field contains the upper limit of each interval. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).

There is also an extended syntax of IntervalMatch including one or several additional key fields. See IntervalMatch (Extended Syntax).

Example:

In the two tables below, the first one defines the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch prefix it is possible to logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.

OrderLog

Start

End

Order

01:00

03:35

A

02:30

07:58

B

03:04

10:27

C

07:23

11:43

D

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

First load the two tables as usual, then link the field Time to the time intervals defined by the fields Start and End:

OrderLog:

LOAD * INLINE [

Start, End, Order

01:00, 03:35, A

02:30, 07:58, B

03:04, 10:27, C

07:23, 11:43, D

];

EventLog:

LOAD * INLINE [

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

];

IntervalMatch (Time) LOAD Start, End Resident OrderLog;

The following table box can now be created in QlikView:

Tablebox

Time

Event

Comment

Order

Start

End

00:00

0Start of shift 1---
01:181Line stopA01:0003:35
02:232Line restart 50%A01:0003:35
04:153Line speed 100%B02:3007:58
04:153Line speed 100%C03:0410:27
08:004Start of shift 2C03:0410:27
08:004Start of shift 2D07:2311:43
11:435End of productionD07:2311:43
Highlighted
MVP
MVP

This is very common question on Community. If you try to search, I can bet, you will get at least 20+ different threads.

Moreover, you can check QlikView Reference Manual...

Happy Qliking !

Highlighted
Master III
Master III

Manish, You are correct seems that people are not satisfied unless and until they post a thread and get some reply from experts.

Highlighted

Hi Palavai,

Assume this Interval match example

1. Interval Match

Purchase:

Load * Inline [

  Supplier, Purchase

  ABC, 125

  DEF, 115  ];

Discount:

Load * Inline  [

  Supplier, From, To, Discount

  ABC, 100, 119, 2%

  ABC, 120, 129, 3%

  ABC, 130, 150, 4%

  DEF, 110, 119, 4%

  DEF, 120, 129, 6%

  DEF, 130, 150, 8%  ];

INNER JOIN IntervalMatch (Purchase,Supplier) Load From, To, Supplier Resident Discount;

Join (Purchase) Load * Resident Discount;

Drop Table Discount;

Drop Fields From,To;

Regards

Anand

Highlighted

Hi,

2. Match Function from Help

match( str, expr1 [ , expr2,...exprN ] )

The match function performs a case sensitive comparison.

Example:

match( M, 'Jan','Feb','Mar')

returns 2 if M = Feb

returns 0 if M = Apr or jan


Regards

Anand

Highlighted

Hi,


3. If condition From Help

if(condition , then , else)

The three parameters condition, then and else are all expressions. The first one, condition, is interpreted logically. The two other ones, then and else, can be of any type. They should preferably be of the same type. If condition is true, the function returns the value of the expression then. If condition is false, the function returns the value of the expression else.

Example:

if( Amount>= 0, 'OK', 'Alarm' )

Ex:- if( PersonDeptName = 'Personal', Amount )

Regards,

Anand

Highlighted
Not applicable

intervalmatch (matchfield) (loadstatement | selectstatement )



Highlighted
Specialist III
Specialist III

Dear Palavai,

Kindly find attached document. There is everything with example just search all of them.

Kind regards,

Ishfaque Ahmed

Highlighted
Not applicable

Writing a question thread: 1-4 lines on the topic, 5 mins typing, xxx waiting for answer

Searching the community: a few key words search on the topic, 10 seconds typing, instant answer

Yea I can totally see the logic in posting the question up