6 Replies Latest reply: Nov 25, 2017 2:39 PM by Marco Wedel RSS

    Time Calculation Help

    Amit Saini

      Hi Folks,

      In the script side , I'm using below logic to match time 21:59:59

      num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without subtraction of 0.0000001 the time 21:59:59 was not matched

       

      What shall I should minus from below to get 20:45:00????

       

      num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

       

      Please help!

       

      Regards,

      AS

        • Re: Time Calculation Help
          Amit Saini

          Any suggestion???

            • Re: Time Calculation Help
              Tresesco B

              What are your input and expected output?

                • Re: Time Calculation Help
                  Amit Saini

                  Hello,

                   

                  What I'm doing is based on Shift table as interval match is looking in CAQ table and taking distinct count of serial number:

                   

                  My Night Shift numbers are 100% matching with data source with below logic

                   

                  IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp

                   

                  But number are not matching for Day shift.

                   

                  Shift Table :

                   

                   

                   

                  Script:

                   

                  Shift:

                  LOAD Shift_Id,

                       Shift_Name,

                       num(Start_Hour) as Start,

                       num(End_Hour - Time#('00:00:01'),'0,0000000') as End,

                       Line_Id,

                       Facility_Id

                  FROM

                  [$(vDataPath)Shift.qvd]

                  (qvd);

                   

                  //exit script;

                   

                  Shifts:

                  //Shift_Early_Late:

                  NoConcatenate

                  LOAD

                      *

                  Resident Shift

                  where End > Start;

                   

                  Shift_Night:

                  NoConcatenate

                  LOAD

                      *

                  Resident Shift

                  where Start > End;

                   

                  Shift_Night_1:

                  Concatenate (Shifts)

                  LOAD

                      Shift_Id,

                       Shift_Name,

                       Start,

                       1 as End,

                       Line_Id,

                       Facility_Id

                  Resident Shift_Night;

                   

                  Shift_Night_2:

                  Concatenate (Shifts)

                  LOAD

                      Shift_Id,

                       Shift_Name,

                       0 as Start,

                       End,

                       Line_Id,

                       Facility_Id

                  Resident Shift_Night;

                   

                  drop tables Shift, Shift_Night;

                   

                   

                   

                   

                   

                  CAQ:

                  LOAD      

                       date(floor(Date.tmp)) as Date,

                       num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without substraction of 0.0000001 the time 21:59:59 was not matched?!

                       year(Date.tmp) as Year,

                       month(Date.tmp) as Month,

                       week(Date.tmp) as Week,

                       Day(Date.tmp) as Day,

                       num(Date.tmp) as NumDate,

                  //dual(year(Date.tmp)&'-'&'Q'&ceil(month(Date.tmp)/3),year(Date.tmp)&ceil(month(Date.tmp)/3)) as Year_Quarter,

                  //    dual(year(Date.tmp)&'-'&month(Date.tmp),year(Date.tmp)&num(month(Date.tmp),'00')) as Year_Month,

                  //    dual(year(Date.tmp)&'-'&week(Date.tmp),year(Date.tmp)&num(week(Date.tmp),'00')) as Year_Week,

                       *

                       ;

                  LOAD

                      *,

                      hour(Date.tmp2) as Time.tmp,

                      IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp  // Logic for night shift

                      ;

                  LOAD

                      num(Date) as Date.tmp2,

                      timestamp(Date) as Date_Org,

                       Facility_Id,

                       Product,

                       Variant,

                       Station,

                       Serial,

                       Characteristic,

                       Values,

                       Status,

                       DTTSERF

                  FROM

                  [$(vDataPath)CAQ_table.qvd]

                  (qvd);

                   

                   

                  left join (CAQ)

                  IntervalMatch (Time,Facility_Id)

                  Load distinct

                      Start,

                      End,

                      Facility_Id

                  Resident Shifts;

                   

                   

                  left join(CAQ)

                  LOAD distinct

                      Facility_Id,

                      Start,

                      End,

                      Line_Id,

                      Shift_Name

                  Resident Shifts;

                   

                  drop table Shifts;

                   

                  Thanks,

                  Amit

                • Re: Time Calculation Help
                  Jonathan Dienst

                  You requirement is not clear.

                   

                  >>What shall I should minus from below to get 20:45:00????

                  >> num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

                   

                  Well it depends on the value of Date,tmp. I am not sure what you are trying to do. To get the value of time 20:45, use

                   

                       =#Time('20:45', 'hh:mm')

                       =MakeTime(20, 45)

                • Re: Time Calculation Help
                  A.M. van Keep

                  Hi Amit,

                   

                  Not quite sure if I'm getting the question right but it seems to me that you can use a FLOOR statement in your script

                  When you use time or datetime it only displays the number in another format it doesn't drop the decimal values after the seconds.

                   

                  DATETIME(FLOOR(Date.tmp *24*60*60)/ (24*60*60))

                   

                  Floor is a function which round the values to the first lower integers, so 5.7 = 5.

                  A date is an integer, while the hours, minutes and seconds are decimal values. Multiplying with 24 makes the hours integers, when adding a multiply of 60 the minutes are integers etc.

                   

                   

                  So floor(date.tmp*24*60*60) / (24*60*60) rounds your date to seconds

                  • Re: Time Calculation Help
                    Marco Wedel

                    Hi,

                     

                    maybe you could use something like

                     

                    Time#(Time(Frac(Date.tmp),'hh:mm:ss'),'hh:mm:ss')


                    to avoid Rounding Errors



                    See also:

                    Correct Time Arithmetic | Qlikview Cookbook  by Rob Wunderlich

                     

                    convert seconds to hh:mm:ss in script


                    Reference DateTime for calculating occupancy



                    hope this helps


                    regards


                    Marco