4 Replies Latest reply: Dec 19, 2013 2:24 PM by Drew Stovall RSS

    While Loop & Variables

      I'm trying to get our clients' life cycles into one table that shows all of their terms.  I have the following data points:


      • Start Date
      • Initial Term Months
      • Renewal Term Months
      • Extension Date
      • Extension Months


      Initial Term = Start Date + Initial Term Months + If (Extension Date < (Start Date + Initial Term Months), Extension Months, 0)

      Renewal Terms = Last Term End + Renewal Term Months + If (Extension Date > Last Term End AND Extension Date < Last Term End + Renewal Term Months, Extension Months, 0)


      The logic is very simple but I'm having a problem pulling in the prior calculated values.  My R.Initial Table already has the initial term calculated.  The logic is that I want the script to continue to run until the client's Current Term's end date is greater than today and then move onto the next client.


      Everything I have works except for getting the variables to update with each successive row.  Once I solve that everything else will work.



      NoConcatenate Load *,

           IterNo() as R.TermNumber,

           If(IterNo() = 1, R.InitialTermEnd,AddMonths($(vPriorTermEnd),R.RenTerm...) as R.TermEnd,

           RowNo() as R.RowNumber,


           Set $(vPriorTermEnd) = If(IterNo() = 1, R.InitialTermEnd,AddMonths($(vPriorTermEnd),R.RenTerm...), //Same formula as R.TermEnd

           Set $(vCurrentFlag) = If(If(IterNo() = 1, R.InitialTermEnd,AddMonths($(vPriorTermEnd),R.RenTerm...) > Today(), 'Current Term','Prior Term')


      Resident R.Initial Until $(vCurrentFlag) = 'Current Term';


      Drop Table R.Initial;