3 Replies Latest reply: Jan 14, 2016 4:49 AM by Matt Pierce RSS

    Script issue with an If statement

    Matt Pierce

      Hi there,

       

      During the load process I'm assessing if a change in record occurs at the end of the month and if it does not force it to be the end of the previous month in a new column End Date Alt.

      I've attempted to do this using an if statement.  I've added an extra column "Check" to assess why I'm having a problem.

       

      See below:

       

      [Data]:

      Load *

          , if(

          date([End Date],'DD/MM/YYYY') = MonthEnd(date([End Date],'DD/MM/YYYY'))

        , date([End Date],'DD/MM/YYYY')

        , MonthEnd(addmonths([End Date],-1))

             ) as [End Date Alt]

          , MonthEnd([End Date]) as Check;

       

       

      Load  * Inline [

      'End Date'

      31/10/2016,

      04/10/2016,

      29/02/2016

      ];

       

      The if statement is not assessing the dates as I would have expected and I'm stumped.

       

      The End Date matches the Check column for both the 29th and the 31st and yet the If statement does not resolve that way.  Clearly I'm doing something wrong. 

       

      End DateEnd Date AltCheck
      29/02/201631/01/201629/02/2016
      04/10/201630/09/201631/10/2016
      31/10/201630/09/201631/10/2016

       

       

      Help!

        • Re: Script issue with an If statement
          Sunny Talwar

          MonthEnd() function outputs a TimeStamp, I think you will need to use Floor() with it to give a desired output here:

           

          [Data]:

          Load *

              , if(

              date([End Date],'DD/MM/YYYY') = Floor(MonthEnd(date([End Date],'DD/MM/YYYY')))

            , date([End Date],'DD/MM/YYYY')

            , MonthEnd(addmonths([End Date],-1))

                 ) as [End Date Alt]

              , MonthEnd([End Date]) as Check;

          Load  * Inline [

          'End Date'

          31/10/2016,

          04/10/2016,

          29/02/2016

          ];

          • Re: Script issue with an If statement
            Stefan Wühl

            MonthEnd(date [, shift = 0])

            Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

            Examples:

            monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'

            monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'

             

            So you need to compare to

             

            if(

                Floor([End Date]) = Floor(MonthEnd( [End Date])),

            ....

             

            [Date() formatting is not needed in numerical comparisons]

            • Re: Script issue with an If statement
              Matt Pierce

              Thanks Both Floor did the trick.  I will have to read up on that a bit more