Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

match the value of a field with a range of values

Hi to all,

I have an excel file with 3 coloumns. The first is the starting value, the second is the final value and the third is a flag (1 or 2) that characterizes all the values in the (final - starting) value range.

eg

start  finish  flag

1        5        D

6        8        D

10      17      C


etc.


I have in my data a field that i want to find out during load time, in which value range it resides so as to add the D or C flag of the corresponding range.


Any help would be greately appreciated.


Thank you all in advance.


Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: match the value of a field with a range of values

You can look into using the INTERVALMATCH LOAD prefix (there is a chapter about that in the HELP / reference manual, or search the forum / blogs).

3 Replies
MVP
MVP

Re: match the value of a field with a range of values

You can look into using the INTERVALMATCH LOAD prefix (there is a chapter about that in the HELP / reference manual, or search the forum / blogs).

Not applicable

Re: match the value of a field with a range of values

Try INTERVELMATCH function. Please find the below from Help File:

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:

SQL SELECT * FROM OrderLog;

SQL SELECT * FROM Eventlog;

IntervalMatch ( Time ) SQL SELECT Start, End FROM OrderLog;

The following table box can now be created in QlikView:

Tablebox

Time

Event

Comment

Order

Start

End

00:00

0 Start of shift 1 - - -
01:18 1 Line stop A 01:00 03:35
02:23 2 Line restart 50% A 01:00 03:35
04:15 3 Line speed 100% B 02:30 07:58
04:15 3 Line speed 100% C 03:04 10:27
08:00 4 Start of shift 2 C 03:04 10:27
08:00 4 Start of shift 2 D 07:23 11:43
11:43 5 End of production D 07:23 11:43
Not applicable

Re: match the value of a field with a range of values

Thank you both for your quick replies.

I looked into the IntervalMatch function and it worked great for me!

Also the example from dathu.qv was very helpfull.

Thanks again!

Community Browser