1 Reply Latest reply: Jun 29, 2017 4:54 PM by Hanief Edwards RSS

    Unexpected Date compare results

    Hanief Edwards

      I am getting following results from the following script. Since 04/02/2017 (row1) is less than May1 (from PriorMonthStart) I expect that _Period1_Flag should be equal to 1 and not 0

       

       

       

      LET cSignificanceInScopeMedium='MEDIUM';
      Let vToDate = Today();
      Set vCurrentMonthStart ='=MonthStart(vToDate,0)';

      Set vPriorMonthStart = '=MonthStart(ToDay(),-1)';

      Table1:
       
      Load * Inline
      [LoanSystemDate, LOANNUMBER,Significance, _ExceptionInScope_Flag
      04/02/2017,123456,MEDIUM,1
      05/02/2017,234567,HIGH,1
      06/01/2017,345678,MEDIUM,1
      06/25/2017,345678,MEDIUM,1
      07/25/2017,345678,MEDIUM,1
      07/20/2017,345678,MEDIUM,1
      ]
      ;
       
      Table2:
       
      Load *
      ,
      If( LoanSystemDate < '$(vPriorMonthStart)', 1,0)
      As _Period1_Flag

      ,
      If(LoanSystemDate >= '$(vPriorMonthStart)', 1,0)
      As _Period2_Flag

      ,
      If(LoanSystemDate >= '$(vCurrentMonthStart)', 1,0)
      As _Period3_Flag

      Resident Table1;
      Drop Table Table1;

       

      Variables created by script look like this:

       

        • Re: Unexpected Date compare results
          Hanief Edwards

          Soluion lay in using Let instead of Set:

           

          Let vToday = Today();
          Let vToDate = Date(Today());

          Let vCurrentMonthStartMonthStart(ToDay(),0);
          Let vPriorMonthStart  =  MonthStart(ToDay(),-1);

          Let vPriorWeekStart = WeekStart(ToDay(),-1);
          Let vPriorFriday  = DayEnd(WeekStart(ToDay(),-1),4);

          Table1:
          Load * Inline
          [LoanSystemDate, LOANNUMBER,Significance, _ExceptionInScope_Flag
          04/02/2017,123456,MEDIUM,1
          05/02/2017,234567,HIGH,1
          05/02/2017,345678,MEDIUM,1
          06/01/2017,456789,MEDIUM,1
          06/25/2017,545678,MEDIUM,1
          07/25/2017,645678,MEDIUM,1
          07/20/2017,745678,MEDIUM,1
          ]
          ;


          Table2:
          Load *
          ,
          If( LoanSystemDate < '$(vPriorMonthStart)', 1,0)
          As _letPeriod1_Flag 

          ,
          If(LoanSystemDate >= '$(vPriorMonthStart)', 1,0)
          As _letPeriod2_Flag

          ,
          If(LoanSystemDate >= '$(vCurrentMonthStart)', 1,0)
          As _letPeriod3_Flag


          Resident Table1;
          Drop Table Table1;