9 Replies Latest reply: Jul 31, 2014 7:11 AM by Marcus Sommer RSS

    Still reworking a code

    Friedrich Hofmann


      Hi,

       

      in an old code, I have introduced, with some help from the Community, a new code to generate minute_intervals between 5:20 am and 10:30 pm. Here is the code.

       

       

      SET vShiftStart=Time#('5:20:00');
      Set vShiftEnd=Time#('22:30:00');
      Plan_Kolli_Temp:
      LOAD

         Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1), 'hh:mm') as Interval_Min
      AutoGenerate (1)
      While Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1), 'hh:mm') <= $(vShiftEnd);
      ;

       

      Later, in a consecutive piece of code, I have the following IF-construct:

      LOAD

      ...

      IF(Interval_Min<'07:30','07:30',
       
      if(Interval_Min<'10:00','10:00',
        
      if(Interval_Min<'12:00','12:00',
        
      if(Interval_Min<'14:00','14:00',
        
      if(Interval_Min<'16:00','16:00',
        
      if(Interval_Min<'18:30','18:30',
        
      if(Interval_Min<'20:30','20:30',
        
      if(Interval_Min<'22:30','22:30','22:30')))))))) as Interval_2H,

        Interval_Min

      RESIDENT Plan_Kolli_Temp

       

      Quite easy it seems, though maybe not the most elegant way of doing this. For some reason, however, the 2h-intervals

      - 07:30

      - 10:00

      - 12:00

      INCLUDE that exact time (the minute-interval 07:30 is included in the 2h-interval 07:30 and the like for the others)

      <=> The later intervals

      - 14:00

      - 16:00

      - 18:30

      - 20:30

      - 22:30

      do NOT include that exact time (the 2h-interval 14:00 includes the minute-intervals up to 13:59 and the like)

       

      => Can anybody tell me why this is so?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

       

        • Re: Still reworking a code
          Joonas Jäspi

          I think you need to have it like this:

          IF(Interval_Min<'=07:30','07:30',
           
          if(Interval_Min<'=10:00','10:00', etc

            • Re: Still reworking a code
              Friedrich Hofmann

              Hi Joonas,

               

              I'll try. But why? What does that '=' change about the operator?

              Thanks a lot!

               

              Best regards,

               

              DataNibbler

                • Re: Re: Still reworking a code
                  Joonas Jäspi

                  For example, if you have time "14:00". Here it checks if it smaller than 14:00

                    if(Interval_Min<'14:00','14:00',


                  It isn't, 13:59 is the last value that makes this statement true. So it tests with next if-sentence, is it smaller than 16:00? (Yes) But in your case you want to have  value 14:00 in "14:00" category. So we add condition that it needs to smaller OR excatly the same value, by adding "=". "<=" could be also written as 

                    if(Interval_Min<'14:00' OR Interval_Min='14:00','14:00',

                    • Re: Still reworking a code
                      Friedrich Hofmann


                      Ah, I understand.

                      I was just confused because you had enclosed that = into the quotes in your post. Of course, the operator <= is known to me ;-)

                      Thanks a lot!

                        • Re: Re: Still reworking a code
                          Joonas Jäspi

                          Oh sorry, typoed

                          • Re: Still reworking a code
                            Friedrich Hofmann


                            No,

                             

                            for some reason it still doesn't work as expected: I have replaced all the '<' operators with '<=', but now the 2H-intervals '10:00' and '12:00' are wrong:

                            I calculate the hr simply with >> Hour(Interval_Min) <<

                            Strangely, some of the intervals are correct, others are not though I am now consistently using the '<=' operator throughout that IF-construct:

                             

                            - The 2hr-interval '07:30' includes that exact time, the hr_intervals are correct: 5, 6, 7

                            - The 2hr-interval '10:00' includes that exact time, the hr_intervals are incorrect: 7, 8, 9, 10 - I don't want 10 in there

                            - '12:00' includes that exact time, hr_intervals are incorrect: 10, 11, 12 - I don't want 12 in there

                            - '14:00' does NOT include that exact time, hr-intervals are correct - 12, 13

                            - '16:00' does NOT include that exact time, hr-intervals are correct - 14, 15

                            - '18:30', '20:30' do include that exact time, that is correct as we have a half-hour here.

                              • Re: Still reworking a code
                                Simen Kind Gulbrandsen

                                And you are sure the exact time 16:00 exists? And that it is not 16:00:00.00001?

                                 

                                try changing Hour(Interval_Min) to Floor(Hour(Interval_Min)) and see if it makes any difference?
                                <= will give you the exact time 16 while < will not.

                                  • Re: Still reworking a code
                                    Friedrich Hofmann

                                    Hi all,

                                     

                                    thanks for the new ideas!

                                    I have some other things to attend to right now - after all, it works, I was just trying to make it more elegant by introducing this code with the minute-intervals - and if I do that, I have to smoothen it out so that it works again. Until I have that, I leave the "live" code as it is.

                                    I will get back to this tomorrow - I really want the "fundaments" of our QlikView_architecture as smooth and trim as they can be.

                                     

                                    Best regards,

                                     

                                    DataNibbler

                                      • Re: Still reworking a code
                                        Marcus Sommer

                                        Hi DataNibbler,

                                         

                                        two other methods could be helpful especially if the condition-check is much larger.

                                         

                                        First you could use class() to create cluster - with none homegenous cluster-size is it more complicated and you will need a few if-loops inside and/or outside.

                                         

                                        Second you could use mapping to a master-timetable or to a mapping-table. Difficult at this is to round your value (Interval_Min) to exactly those values which you have in mapping.

                                         

                                        - Marcus