5 Replies Latest reply: Oct 26, 2013 4:09 PM by Andrew Pettit RSS

    days between two dates

      Hello,

        I have been all week trying to load a year's worth of payroll.  It is not going well because this routine, I have used in the past but it does not seem to be working correctly. 

      Is there a better way to determine the number of days between two dates.

      The scenario is that the .csv file contains hire date in the format ccyyddmm as a string and the employment record contains a datetime.

      I first attempt to convert the string to a date time and then use datetime.elapse to get the number of days.

      It is not producing reliable results.  Here is the code.

      Any suggestions or spotting of issues.

      Thanks Traci

       

      function newdate(d)

        valid, value = pcall(string.datetime, d,"CCYYMMDD");

        if valid then return value else return nil end;

       

        

      end;

      function filter(input)

      local daysfrom=777;

       

      local hiredatecsv = newdate(input.EMPLOYEE_ADJ_HIRE_DATE);

      --log.notice(tostring(hiredatecsv));

       

      if (hiredatecsv==nil) then daysfrom =999; end;

      if (input.HireDate ==nil) then daysfrom =999;end;

      --log.notice(daysfrom);

       

      if (daysfrom~=999) then daysfrom = datetime.elapse(hiredatecsv,input.HireDate, d); end;

      if (daysfrom~=nil) then log.notice(tostring(daysfrom)); end;

       

        

      return daysfrom<30

       

      end;

        • Re: days between two dates

          In the datetime.elapse function, you need to put quotation marks (single or double) around the last argument.  That is, datetime.elapse(hiredatecsv,input.HireDate, "d");

           

          Also, to test for nil, you might want to use the is.null function instead of ==nil.

           

          • Re: days between two dates

            Thanks John,  I wondered if something happened to my syntax...It is working much better.

            Traci

            • Re: days between two dates
              Andrew Pettit

              Out of curiosity, if you are loading data why not simply perform the date calculation as part of the load script?  Correct me if I am wrong but appears your trying to do it in a macro currently.

               

              You can use Date#(YourDateFieldHere,'YYYYMMDD') to convert the date formats to valid QlikView dates, and then simply subtract the one date from the other in order to get the number of days between the two dates.  If you want to do some type of default if the date doesn't convert properly use the Alt function to provide an alternative value in the case the Date# returns a null value.

               

              Example:

               

              MyTable:

              LOAD

                   Alt( Date#( StartDate , 'YYYYMMDD' ) , Today() ) as StartDate,

                   Alt( Date#( EndDate , 'YYYYMMDD' ) , Today() ) as EndDate

                   Alt( Date#( EndDate , 'YYYYMMDD' ) , Today() ) - Alt( Date#( StartDate , 'YYYYMMDD' ) , Today() ) as DaysElapsed

              FROM

                   ....

              ;

               

              Hope this is helpful.