3 Replies Latest reply: Oct 13, 2015 5:52 AM by Francisco Javier Peri??n Delgado RSS

    Start time - end time to sections of 15 minutes

    Francisco Javier Peri??n Delgado

      Hello everyone, I'm trying to match a start time and an end time to all the posible differents sections of 15 minutes in his range, for example:

       

      If I have a record with start_time = 12:05 and end_time = 13:25, I need to relate this record to all this sections:

       

      12:00 - 12:14

      12:15 - 12:29

      12:30 - 12:44

      12:45 - 12:59

      13:00 - 13:14

      13:15 - 13:29

       

      With IntervalMatch I've been able to match the first and the last section, but I need all of them. How can I do this?

       

      Thanks for your help.

        • Re: Start time - end time to sections of 15 minutes
          Onno van Knotsenburg

          I don't see why you could not do all these sections with intervalmatch. Could you show us the code you currently used?

           

          Might be easier to achieve with the class function by the looks of it, as they all of the same length.

            • Re: Start time - end time to sections of 15 minutes
              Francisco Javier Peri??n Delgado

              This is the code I'm using:


              Login_Time:

              LOAD

                Login,

                Login_Time,

                Start_time,

                End_time

              Resident Login_Logoff;

               

              Tramos_15:

              LOAD * INLINE [

                  Tramo, Inicio, Fin

                  00:00-00:15, 0:00, 0:14

                  00:15-00:30, 0:15, 0:29

                  00:30-00:45, 0:30, 0:44

                  00:45-01:00, 0:45, 0:59

                  01:00-01:15, 1:00, 1:14

                  [...]

                  23:00-23:15, 23:00, 23:14

                  23:15-23:30, 23:15, 23:29

                  23:30-23:45, 23:30, 23:44

                  23:45-00:00, 23:45, 23:59

              ;

               

              Left Join IntervalMatch(Start_time)LOAD Inicio, Fin Resident Tramos_15;

              Left Join (Tramos_15) LOAD * Resident Login_Time;

              Drop Table Login_Time;

               

              To relate all the sections this way I would need to write a table with all the possible combinations and it would result in an monstruous table.

               

              Thank you for your help.

            • Re: Start time - end time to sections of 15 minutes
              Avinash R

              Since your interval are fixed you can try with class function .

              class(expression, interval [ , label [ , offset ]])

              Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

              Examples:

              class( var,10 ) with var = 23 returns '20<=x<30'

              class( var,5,'value' ) with var = 23 returns '20<= value <25'

              class( var,10,'x',5 ) with var = 23 returns '15<=x<25'


              You can also try with the intervalMatch

               

              IntervalMatch (Extended Syntax)

              The extended IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals, while at the same time matching the values of one or several additional keys.

              This is a very powerful and flexible feature that can be used for linking transactions with dimensions that are changing over time: Slowly changing dimensions.

              The IntervalMatch prefix must be placed before a Load or Select (SQL) statement that loads the intervals. The table containing the discrete data points and the additional keys must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix transforms the loaded table of intervals and keys 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, interval and value of the key field(s).

              The syntax is:

              intervalmatch (matchfield,keyfield1 [ , keyfield2, ... keyfield5 ] ) (loadstatement | selectstatement )

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

              keyfield(s) are fields that contain the additional attributes that are to be matched in the transformation.

              loadstatement or selectstatement must result in a table where the first two fields contain the lower and upper limits of each interval and the third and any subsequent fields contain the keyfield(s) present in the IntervalMatch statement. 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).

              In order to avoid undefined interval limits being disregarded, it may be necessary to allow NULL values to map to other fields that constitute the lower or upper limits to the interval. This can be handled by the NullAsValue statement or by an explicit test that replaces NULLs with a numeric value well before or after any of the discrete numeric data points.

              Example:

              Inner Join IntervalMatch (Date,Key) LOAD FirstDate, LastDate, Key resident Key;