8 Replies Latest reply: Oct 23, 2017 6:57 AM by Björn Bengtsson RSS

    Intervalmatch

    Björn Bengtsson

      Hi Gents,

       

      I want to create a Intervalmatch with what I think is a text-string.

      I have a field with the name "Start" from table "StartTable" and it contains minutes that counts from 00:00 (For example 10:00 is 600 minutes, 0200 is 120 minutes etc)


      In the loading script I have already made a interval to convert it to hours (with a INTERVAL) but I guess I can't use that field in the loading script (?).

       

      I have started to type a IntervalMatch..

       

      TimeGroup:

      LOAD * INLINE[

      Start, Stop, TimeGroup

      00:00(0), 06:00(360) >=00:00 < 06:00 (360 minutes)

      06:01(361), 08:00(480) >=06:00 < 08:01 (481)

      08:01 (481), 10:00 (600) >=08:01 <10:01 (601)

      10:01 (601), 12:00 (720) >=10:01 <12:00 (720)

      And further on...

       

      Then the next step is to make a Interval Match

      IntervalMatch (StartTable)

      LOAD

           Start

      Resident TimeGroup;

       

      Load the data - > I will get a Synthetic key and then I type this to get rid of it:

      IntervalMatch (StartTable)

      Left Join (TimeGroup)

      LOAD

           Start

      Resident TimeGroup;

       

      Load Data and end it with

      Drop fields Start;

       

      And then load data one last time.


      So my question after this novel:

      1) I have to convert the text-string to a number?

      2) How do I write the TimeGroup table correctly so i get it in the format HH:MM? Can I use this and how?

      (Interval("START"/24/60, 'hh:mm') as StartHours

      3) Any more trouble I might get into?

       

      Thanks in advance

        • Re: Intervalmatch
          Eduardo DImperio

          Hi Bjorn (sory my kb doesn't have accent),

           

          I don't understand what's you input and output.

           

          You have a start time, end time and whant the time between?

           

          I don't get it.

          • Re: Intervalmatch
            Björn Bengtsson

            Hi Eduardo,

             

            (No worries about the accent, Sweden have some downsides: High taxes, bad weather and it's accent..)

             

            Look at it as a Marathon-race where the participants starts on different slots.
            I want to be able to list how many that started between 0800-0900, 09:01-10:00 and so on.

             

            Thanks again.

            • Re: Intervalmatch
              Juraj Misina

              Hello Bjorn,

               

              you do not necessarily need to convert string to number, it is sufficient to use number interpretation function, Interval#(Start, 'hh:mm') in your case. As for IntervalMatch, I believe you need to provide to fields when loading. In fact, I'd do it in oposite direction compared to what you do. Plus, IntervalMatch takes a field name as an argument, therefore I would avoid using the same field name in StartTable and TimeGroup table. Having loaded the StartTable with Start field and TimeGroup table with StartTime and EndTime (instead of Start/End, just to avoid common field names), I'd continue as follows:

              //attach interval fields to the Start field

              IntervalMatch(Start)

              Left Join (StartTable)

              LOAD

              StartTime,

              EndTime

              Resident TimeGroup;

               

              //attach TimeGroup label to respective intervals

              Left Join (StartTable)

              LOAD * Resident TimeGroup;

               

              //drop TimeGroup table to clean up

              Drop Table TimeGroup;

               

              Hope this helps.

              Best

              Juraj

                • Re: Intervalmatch
                  Björn Bengtsson

                  Hi Juraj,

                   

                  Thanks for taking your time on this.

                   

                  What I have done now:

                   

                  In StartTable I have renamed "START" to TimeStart.

                  Then I made a class-function to make intervals and to create the field TimeGroup (Class("START", 480, 'TimeStart') as TimeGroup,). I also used the replace-function to simplify the text output ('>= ' & REPLACE(CLASS("START", 480, 'TimeStart'), '<= TimeStart <', ' < ') AS TimeGroup)

                   

                  Then i commented out this and created my intervals with several IFs

                  IF("START">=0 AND "START" <360,'>= 0 < 360',

                      IF("START">=360 AND "START" <480,'>= 360 < 480',

                          IF("START">=480 AND "START" <600,'>= 480 < 600',

                              IF("START">=600 AND "START" <840,'>= 600 < 840',

                                  IF("START">=840 AND "START" <1080,'>= 840 < 1080',

                                      IF("START">=1080 AND "START" <1440,'>= 1080 < 1440')))))) as TimeGroup,

                   

                  When I was done here, I created my TimeGroup table

                   

                  TimeGroup:

                  LOAD * INLINE [

                  Start1, Stop1, TimeGroup

                  0, 360, >= 0 < 360

                  360, 480, >= 360 < 480

                  480, 600, >= 480 < 600

                  600, 840, >= 600 < 840

                  840, 1000 >= 840 < 1000

                  1000, 1440 >= 1000 < 1440

                  ];

                   

                  No problems yet but now I meet the cavalry:

                   

                  I get a circular reference and a synthetic key when I load the script below.

                   

                  IntervalMatch (TimeStart)

                  Load

                  Start1,

                  Stop1

                  Resident TimeGroup

                   

                  When I add the Left Join (TimeGroup) under IntervalMatch the circular reference disappeard, but the synthetic key is still there. Even after i drop the fields Start1 and Stop1.

                   

                  So my first challenge is to remove the synthetic key and then my challenges are

                  How to get the minutes into HH:MM when i vizualize and som set-analysis-expression.

                   

                  Thanks in advance!

                    • Re: Intervalmatch
                      Juraj Misina

                      Bjorn,

                       

                      I've got lost here. You create your TimeGroup via nested IFs - which is OK -  and at the same time you create yet another TimeGroup inline which you want then join via IntervalMatch?

                       

                      Here's some very simple (but working) example for IntevalMatch use which may help you:

                      Tmp_Intervals:
                      LOAD * Inline [
                      ID,    Start,    End
                      A,    6,    8
                      B,    2,    15
                      C,    9,    20
                      D,    1,    8
                      E,    8,    15
                      F,    10,    15
                      G,    6,    9
                      H,    8,    9
                      ];
                      
                      
                      Elements:
                      LOAD
                          IterNo()    as Element
                      AutoGenerate 1 While IterNo()<20;
                      
                      Intervals:
                      IntervalMatch(Element)
                      LOAD
                          "Start",
                          "End"
                      Resident Tmp_Intervals;
                      
                      Left Join(Intervals)
                      LOAD
                          ID,
                          "Start",
                          "End"
                      Resident Tmp_Intervals;
                      
                      Drop Table Elements;
                      

                       

                      Getting minutes to hh:mm format is fairly simple. You only need to get the that number to a fraction of 1. So 360 minutes would be 360/60/24=0,25 (integer part=date/number of days, decimal part = time), so you can do Inteval(START/60/24, 'hh:mm').

                       

                      Hope this helps.

                      Juraj