2 Replies Latest reply: Jun 11, 2018 6:53 AM by Martin Hamilton RSS

    Evaluating a generated date as part of load script?

    Martin Hamilton

      Hi

       

      I am calculating a date based on 2 values being passed into load script below:

         

      Sub_Contract_Start_Date,

      Sub_Contract_Term,

      AddMonths ([Sub_Contract_Start_Date],[Sub_Contract_Term]) as [Sub_Contract_End_Date]

       

      What I want to also do is identify any of the dates I create as part of the load which are within the next 30 days i.e. the date created which is named Sub_Contract_End_Date and give them an indicator e.g. contract expiring in next 30 days.

       

      Is it possible to do this within the same load script or do I need to create a resident table to do the calculation?

       

      I was thinking extending the expression with an IF statement but wasnt sure whether that was possible.

       

      Any help appreciated.

       

      thanks

       

      Martin

        • Re: Evaluating a generated date as part of load script?
          Sunny Talwar

          You should be able to do this in the same load statement, but I am just not sure what is this getting compared to? Today? may be this

           

          Sub_Contract_Start_Date,

          Sub_Contract_Term,

          AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) as [Sub_Contract_End_Date],

          If(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() <= 30, 1, 0) as ContractExpiringFlag

          • Re: Evaluating a generated date as part of load script?
            Martin Hamilton

            Hi Sunny this worked great for me, made some amends to your original solution which now allows me to generate a contract status based on the data.

             

            If(Interval(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today()) >0 and Interval(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today()) <30, 'Contract Ending',if(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() >30,'In Contract','Out Of Contract')) as Sub_Contract_Status

             

            I think i have it right but many thanks for the steer.

             

            cheers

             

            Martin