3 Replies Latest reply: Nov 27, 2013 5:04 AM by Dimitris Charalampou RSS

    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.


       

        • Re: match the value of a field with a range of values
          Stefan Wühl

          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).

          • Re: match the value of a field with a range of values
            Srikanth P

            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
            • 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!